topic Re: Cumulative Total Chart with 2+ Dimensions in QlikView

Cumulative Total Chart with 2+ Dimensions

Thu, 08 May 2014 12:52:15 GMT

I'm stuck on a problem which should be very simple in theory - I'm wanting to produce a line chart showing cumulative sum of Sales across the months in a year, but with multiple lines based on a second dimension, e.g. Product line.

I have tried using the rangesum(above()) method which works fine in straight table as I can control the sort order. However, when I turn it into a line chart I am forced to place the Product dimension second in order to cut by it, which then makes the above() function refer to the previous product in the same month, rather than the same product in the previous month.

I've attached a sample qvw showing my attempt, and an Excel file showing what it should produce.

I'm hoping there's a simple solution as it's a very common use case. Note that I need a solution that does not involve scripting, since I need it to be fully dynamic in real cases and respond to multiple filters and potentially set analysis within the expressions.

Re: Cumulative Total Chart with 2+ Dimensions

male_carrasco — Thu, 08 May 2014 14:17:52 GMT

Hi,

Try this.

Re: Cumulative Total Chart with 2+ Dimensions

Thu, 08 May 2014 14:23:40 GMT

So simple after all, works fine, thanks.

I've tested on a few slightly more complicated cases like proportion of year total, and that works too. I'll test on some real cases later and see if it gets stuck anywhere.

Re: Cumulative Total Chart with 2+ Dimensions

josephinetedesc — Mon, 09 Feb 2015 05:51:44 GMT

Hi I am trying to do the same thing but ... I have formulas for the different column headings and so the choice of Expressions| Accumulations is greyed out as a table. I am not sure how to do the cummulative change as a table.

I have looked up possible threads but they all seem very difficult.

Date_Fin	2014/15	2014/15	2014/15
Date_Fin_Month	Sales ACTUAL	Sales TARGET	ACTUAL - TARGET	cumm change
Jul	25	24	1	1
Aug	23	25	-2	-1
Sep	22	26	-4	-5
Oct	20	26	-6	-11
Nov	19	26	-7	-18
Dec	12	27	-15	-33
Jan	15	27	-12	-45

Re: Cumulative Total Chart with 2+ Dimensions

josephinetedesc — Mon, 09 Feb 2015 06:31:09 GMT

it does work now ... i made it a pivot table and it works beautifully and it now has 2 columns ... so the answer must be the type of table you use?