Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.