5 Replies Latest reply: Feb 14, 2013 11:33 AM by Jeroen Jordaan RSS

    Set Expression doesn't seem to work in FirstSortedValue

    Jeroen Jordaan

      Hello everyone,

       

      In my dashboard I have to create an expression which does the following thing.

       

      I want to know wich Item has the highest Index Margin.

      For this KPI I created een expression with the FirstSortedValue function.

       

      =FirstSortedValue(ItemNo, -Aggr(((Sum({<VEItemLedgerTypeCode = {1}>} VESalesAmountActual

      -

      - VECostAmountActual))

      /

      Sum({<VEItemLedgerTypeCode = {1}>}VESalesAmountActual)

      *

      100),ItemNo))

       

      With this expression I know wich Item has the highest Index Margin.

      Only now I want to create an expression where I can set the store id.

      I created an expression like this;

       

      =FirstSortedValue({<StoreNo = {'110_Base'}>} ItemNo, -Aggr(((Sum({<VEItemLedgerTypeCode = {1}>} VESalesAmountActual

      -

      - VECostAmountActual))

      /

      Sum({<VEItemLedgerTypeCode = {1}>}VESalesAmountActual)

      *

      100),ItemNo))

       

      I also tried to create a set expression where the expression does not react on any selection in the dashboard. Normaly you can do this with an 1 like this

       

      =FirstSortedValue({1}ItemNo, -Aggr(((Sum({<VEItemLedgerTypeCode = {1}>} VESalesAmountActual

      -

      - VECostAmountActual))

      /

      Sum({<VEItemLedgerTypeCode = {1}>}VESalesAmountActual)

      *

      100),ItemNo))

       

      But also this doesn;t seem to work.

       

      However the expression works the same as the first one. It seems that the expression does not react on the given set expression ({<StoreNo = {'110_Base'}>})

       

      Eventually I want to achieve the following;

      Sales * the highest Index Margin over all stores - the highest Margin from a specific store.

       

      I hope someone can help me.

       

      Thanks in advance