Re: cumulative analysis - Qlik Community

Report Inappropriate Content · ‎2012-05-24

suppose i have two column date and sales

2006 2300

2007 2700

2008 3100

2009 3500

2010 4500

2011 3900

when i select year 2010, i want show the sales from year 2006 to 2010

swuehl · ‎2012-05-24

So you want to see sales from minimum year to selected year?

Try this as expression (in a text box or as expression in a chart with dimension year):

=sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales)

Report Inappropriate Content · ‎2012-05-25

You could also plot a chart with 'year' as the dimension and sales as the expression.

Then in the expressions tab of the properties of the chart, select Full Accumulation and Text as Pop-up

You can then see the cumulative sales over all years at once!

-Nick

Report Inappropriate Content · ‎2012-05-26

@swuehi

I want the solution for pivot table along with two dimension.

And suppose that when i select a year 2010 , i should see the sales as 16100

=sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales) , this expression work in text but when i used in chart in does'nt work..

I want see the Following output

2007 2300

2008 5000

2009 8100

....

...

swuehl · ‎2012-05-26

Ok, you haven't mentioned a pivot table and a second dimension so far...

You can get the cumulative in a pivot table using rangesum() function together with chart inter record functions like above. Note that the appropriate chart inter record function will depend on your chart layout. I assume your year is the dimension to the left and second dimension (maybe Customer) is dragged to the top.

Then you can use something like:

=rangesum(above(sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales),0,RowNo()))

Hope this helps,

Stefan

Report Inappropriate Content · ‎2012-05-28

Above expression was correct for pivot table..

If possible can i see the folowing output:-

after Apply the formula my pivot table look like this

2007 20

2008 50

2009 90

2010 140

2011 200

2012 270

Suppose now i select year 2009, the output should be like this

2009 90

swuehl · ‎2012-05-29

You can try just adding an aggr() advanced aggregation to your expression:

=aggr(

rangesum(above(sum({<year = {">=$(=min({1} year))<=$(=max(year))"} >} sales),0,RowNo()))

, customer, year)

where customer is your second dimension and year your dimension you want to filter.

To make this work, the load order of year must be chronological (because the aggr() function sorts its dimension in load order, not in the order you set in table properties).