Using Min Function inside Where

[former_member290786]

Hello,

Using 4.1 SP4, I'm having trouble calculating something:

I have Item dims- Item Group, Item Size(code), and sellout quantity. (the data provider itself is more detailed but the table only shows the mentioned dimensions)

I would like to calculate for each Item group, the sellout quantity for the Minimum value of the size and divide by the sellout quantity for the maximum value.

The sizes are in codes, and are sorted (if value 9 is the minimum value, thats the value i want to calculate).

ex. If some item group has 3 sizes:

Item Group || Size || Size Code || Sellout

A || 1000ml || 10 || 10,000

A || 500ml || 9 || 7,000

A || 200ml || 4 || 5,000

I would like to calculate 5,000/10,000 (4 is the minimum value for size code, and 10 the is maximum value).

When I calculate

[Sellout] Where ([Size Code]=4) I get the "5,000",

but when I try [Sellout] Where ([Size Code]=Min([Size Code])

Does anyone have a suggestion how can i refrence the where to the value of the correct size code only.

Please your kind help.

Thank you,

Or

[Former Member]

I believe you are trying to achieve result similar to below result set based on Efashion.

i.e Min/Max at State Level

Here is the formula which I used for the same.

=max([Sales Revenue] ForEach([City]))/min([Sales Revenue] ForEach([City]))

or another version

=max([Sales Revenue] in ([State];[City]))/min([Sales Revenue] in ([State];[City]))

I hope, you should be able to get what you want using this.

[Former Member]

OK,

sorry,

are you intending to use this value with which dimension?

FOr a more generic value,

try ([sellout] where (Min([size code] ) In ([group]) = [size code]) ForEach ([Item];[[size];[group];[sellout]))

CHeers,

ROgerio

[former_member290786]

Thanks ROgerio,

But I didn't understand, you want me to try the entire formula? seems like a boolean formula:

([sellout] where (Min([size code] ) In ([group]) = [size code]) ForEach ([Item];[[size];[group];[sellout]))

or just the left part of the equation?

Thanks,

Or.

[Former Member]

I Would like you to try the entire formula,

CHeers,

Rogerio

[former_member290786]

Gives me back #MULTIVALUE

tried to add sum, but that way I get the same number to each group.

(by the way I don't have [item] dimension, so I've allowed myself to subtract it from your formula

Thank you for that or any other idea.

Or.

[Former Member]

Hi,

try creating a var for Min, varmin= Min([ Size Code]) In([Item Group])

and then valsellout = =[ Sellout] Where ([varmin]=[ Size Code])

[Former Member]

Hello,

You can try this;

=([Sellout] Where ([Size Code]=Min([Size Code]) in block))/([Sellout] Where ([Size Code]=Max([Size Code]) in block))

Regards,

Utku

[former_member290786]

Hi Utku,

The solution is not returning an error, but the min \ max values I get are according to the entire block, and that means for all of the groups - I just need the min\max for the specific Item Group.

Have any other suggestion?

Thank you,

Or.

[Former Member]

Hi,

If you are going to show both  Item Group || Size || Size Code || Sellout and if you want to calculate min/max foreach item group, here is the formula;

[Cal]=(min([Sellout]) in([Item Group]))/(max([Sellout]) in([Item Group]))

Item Group || Size || Size Code || Sellout || Cal

Other wise, can you show me what you want to see in the table?

Regards,

Utku

[former_member290786]

Hello Utku,

In the table I want to show the Group only - Can you please provide me with the correct formula for that scenario?

Thank you,

Or.

[Former Member]

HI,

try Min([sellout]in ([group]))

CHeers,

ROgerio

[former_member290786]

Hi,

What does it have to do with the request?

I'm sorry but I don't need the min sellout, I need the sellout(which is a sum measure) for the minimum item size on each group.

its something like - [sellout] where (min([size]) In ([Group]))

but doesn't work in the all variations I've tried.

Thank you,

Or.