CODEWITHSUNDEEP
conditional-statementsqlikview
JNM
JNM

Reputation: 1195

QlikView Conditional SUM current value

I have a pivot table with multiple rows dimensions (District, Region, Shop) and months in columns. I need to calculate sales Growth ((sales this month - sales previous month)/sales previous month). As You can see from formula, i need a value from previous month (previous column). I have a formula for testing:

Sum(Total {<District={'District1'}, [Month]={'2015 09'}>} Quantity)

With this formula i am able to get the value from previous column (lets say i am calculating growth in month 2015 10 and District1). The problem is, that i get a wrong value, when this formula is used in a row with different District. Is there any way to get district value of current row and use it in a formula? I tried multiple variations like:

Sum(Total {<District={$(District)}, [Month]={'2015 09'}>} Quantity)
Sum(Total {<District={$<=District>}, [Month]={'2015 09'}>} Quantity)

but none of them work

Upvotes: 1

Views: 6315

Answers (1)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

There is no need to add District in the calculation. QV will aggregate to it automatically since District is dimension already.

If you want to aggregate all rows under each District to show the total quantity for the whole District then you can use the following expression:

sum( total <District> Quantity )

The picture below demonstrate the result:

enter image description here

Also you can check the QV help (c:\Program Files\QlikView\English.chm) for the Aggr() function as well. With Aggr() function the same result can be achieved with:

aggr( NoDistinct sum( Quantity ), District )

Upvotes: 0

Related Questions