Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I resolved the issue.
I added another a second dimension in the Aggr function.
QlikView Specialists,
Is there anyone that can help me?
I hope that you can create some time for me to help with this problem.
thanks
I hope somebody can help me with this issue!
I think you need to do the sum in the script and use firstsortedvalue after that, so resolve
Aggr(((Sum({<VEItemLedgerTypeCode = {1}>} VESalesAmountActual
-
- VECostAmountActual))
/
Sum({<VEItemLedgerTypeCode = {1}>}VESalesAmountActual)
*
100),ItemNo)) using group by
your expression will be
aggr(FirstSortedValue( ItemNo, - SalesTotalResult), ItemNo)
Hope this helps
José,
Thanks for your reaction.
The calculation of the Index Margin is working fine.
The only that isn't working is if I want to calculated for a certain store.
I'm using the expression below.
=FirstSortedValue({<StoreNo = {'110_Base'}>} ItemNo, -Aggr(((Sum({<VEItemLedgerTypeCode = {1}>} VESalesAmountActual
-
- VECostAmountActual))
/
Sum({<VEItemLedgerTypeCode = {1}>}VESalesAmountActual)
*
100),ItemNo))
With a set expression ({<StoreNo = {'110_Base'}>}) I only want to calculated the Index Margin of that store.
But with this expression I also get the result over all stores
I resolved the issue.
I added another a second dimension in the Aggr function.