Set Expression doesn't seem to work in FirstSortedValue

[jjordaan]

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

[jjordaan]

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

[jjordaan]

I hope somebody can help me with this issue!

[chematos]

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

[jjordaan]

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

[jjordaan · Accepted solution]

I resolved the issue.

I added another a second dimension in the Aggr function.