Problem with sum (firstsortedvalue)

Fri, 12 Jul 2013 16:51:17 GMT

Hi,

I'm having problems with the following expression.

If I use like this, it works perfectly:

if(((DIM_A=23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000) or (DIM_A<>23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000))

,FirstSortedValue(DIM_B, -DIM_C))

If I try to sum the results, it does not work (Error in expression):

Sum(

if(((DIM_A=23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000) or (DIM_A<>23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000))

,FirstSortedValue(DIM_B, -DIM_C))

)

I've also tried to insert the sum inside if statement, but nothing changes....

if(((DIM_A=23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000) or (DIM_A<>23110 and FirstSortedValue(DIM_B, -DIM_C)<=5000000))

,Sum(FirstSortedValue(DIM_B, -DIM_C)))

Any idea?

Thanks in advance,

Gabriele

Re: Problem with sum (firstsortedvalue)

Gysbert_Wassenaar — Fri, 12 Jul 2013 16:56:32 GMT

Both sum and firstsortedvalue are aggregation functions. That means you need the aggr function in between. Something like:

sum(aggr( if(....etc ) , MyDim1, MyDim2,... etc) )

Replace MyDim1, MyDim2,....etc with the dimensions over which you want to aggregate the results of the firstsortedvalue expression. Usually those are the same as your chart dimensions.

topic Re: Problem with sum (firstsortedvalue) in QlikView

Problem with sum (firstsortedvalue)

Re: Problem with sum (firstsortedvalue)