topic cumulative analysis in QlikView

cumulative analysis

Thu, 24 May 2012 06:37:03 GMT

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

cumulative analysis

swuehl — Thu, 24 May 2012 08:20:23 GMT

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)

cumulative analysis

Fri, 25 May 2012 20:09:45 GMT

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

cumulative analysis

Sat, 26 May 2012 09:56:44 GMT

@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

....

...

cumulative analysis

swuehl — Sat, 26 May 2012 13:26:25 GMT

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

cumulative analysis

Mon, 28 May 2012 05:43:08 GMT

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

Re: cumulative analysis

swuehl — Tue, 29 May 2012 12:26:37 GMT

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).

Re: cumulative analysis

Tue, 12 Feb 2013 16:34:32 GMT

One problem still remains: if you have periods with no data this expression displays missing. It's no good.

Re: cumulative analysis

Mon, 18 Feb 2013 12:25:28 GMT

You could create new table where each date from your sales corresponds to that date and all dates after that date. F.e.:

Date DateCum

2006 2006

2006 2007

2006 2008

2007 2007

2007 2008

2008 2008

Then try to use DateCum as a dimension in Bar Chart. I could share script generating such a table if you need it.