topic Re: Set Expression doesn't seem to work in FirstSortedValue in QlikView

Set Expression doesn't seem to work in FirstSortedValue

jjordaan — Thu, 07 Feb 2013 08:19:51 GMT

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

Re: Set Expression doesn't seem to work in FirstSortedValue

jjordaan — Mon, 11 Feb 2013 09:58:22 GMT

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

Re: Set Expression doesn't seem to work in FirstSortedValue

jjordaan — Wed, 13 Feb 2013 10:05:24 GMT

I hope somebody can help me with this issue!

Re: Set Expression doesn't seem to work in FirstSortedValue

chematos — Wed, 13 Feb 2013 12:58:10 GMT

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

Re: Set Expression doesn't seem to work in FirstSortedValue

jjordaan — Wed, 13 Feb 2013 14:14:14 GMT

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

Re: Set Expression doesn't seem to work in FirstSortedValue

jjordaan — Thu, 14 Feb 2013 16:33:21 GMT

I resolved the issue.

I added another a second dimension in the Aggr function.