Dividing accumulating expressions

Fri, 19 Nov 2010 18:30:54 GMT

Hi, I have the same problem

I have an expression as follows: its name is "CASH FLOW"

sum ( {<class = {a}>} [Balance] / sum ( {<{class = {b}>} [Balance])

when I accumulate = it accumulates the ratios - which is not what I want

I want - "cumulative" sum ( {<class = {a}>} [Balance] / "cumulative" sum ( {<{class = {b}>} [Balance])

The dimension is period i.e. Year 1, 2, 3 etc

Dividing accumulating expressions

johnw — Fri, 19 Nov 2010 23:25:17 GMT

From the other thread:


John Witherspoon wrote: Rangesum(above()) should still work. Create separate hidden expressions for the class a and class b balances, then rangesum each over the current row and all above rows. Probably something like this:
Class A Balance = sum({<class={'a'}>} Balance) // hidden expression
Class B Balance = sum({<class={'b'}>} Balance) // hidden expression
Cash Flow = rangesum(above("Class A Balance"),0,rowno())
 /rangesum(above("Class B Balance"),0,rowno())
A better approach might be to handle it with data, though. Let's say your first year was 2001:
AsOfYear, Year
2001, 2001
2002, 2001
2002, 2002
2003, 2001
2003, 2002
2003, 2003
etc.
Use AsOfYear in the chart, and the sums will be a full accumulation (don't set the full accumulation option), and will work when dividing the sums.

topic Dividing accumulating expressions in QlikView

Dividing accumulating expressions

Dividing accumulating expressions