Skip to Content

Using Min Function inside Where

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Dec 25, 2014 at 02:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • avatar image
    Former Member
    Jan 01, 2015 at 09:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Or Ben-Porath

      Hi,

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

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

  • avatar image
    Former Member
    Jan 02, 2015 at 10:15 AM

    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.


    eFashion.png (42.2 kB)
    Add comment
    10|10000 characters needed characters exceeded