Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

phillipmason
New Contributor

Bar Chart with running total and multiple dimensions

Hello

I'm trying to create a simple bar chart with a running/cumulative total by month. 

I can get this to work fine with just one dimension and a measure but when I add a second dimension it doesn't work

I've seen similar issues raised but the answers to those doesn't seem to work for me. 

For the measure I'm using the expression 

Aggr(RangeSum(Above(Sum(LBS), 0, RowNo())), [INSTALLDATE.autoCalendar.YearMonth], SCHEMESUBGROUP) 

Dimensions are Schemesubgroup and INSTALLDATE.autoCalendar.YearMonth

This gives me 

bar chart.PNG

 

I need 

 

cumulative bar chart 2.PNG

 

Is this possible?

 

thank you . Phillip

Labels (1)
1 Solution

Accepted Solutions

Re: Bar Chart with running total and multiple dimensions

I think you need to do some work in the script first.  Say you have loaded your Excel file with the three columns (INSTALLDATE, SCHEMESUBGROUP, LBS) into a table named "Data".  You could calculate a cumulative sum in the script like this:

Final:
LOAD INSTALLDATE,
	 SCHEMESUBGROUP,
     LBS,
     RANGESUM(LBS, IF(PREVIOUS(SCHEMESUBGROUP) = SCHEMESUBGROUP, PEEK(CUMULATIVELBS))) AS CUMULATIVELBS
RESIDENT Data
ORDER BY SCHEMESUBGROUP, INSTALLDATE;

DROP TABLE Data;

 

Then for your bar chart:
Dimension: =MONTHNAME(INSTALLDATE) //or your [INSTALLDATE.autoCalendar.YearMonth] field
Dimension: SCHEMESUBGROUP
Expression: MAX(CUMULATIVELBS)
bar_chart.png

View solution in original post

2 Replies

Re: Bar Chart with running total and multiple dimensions

I think you need to do some work in the script first.  Say you have loaded your Excel file with the three columns (INSTALLDATE, SCHEMESUBGROUP, LBS) into a table named "Data".  You could calculate a cumulative sum in the script like this:

Final:
LOAD INSTALLDATE,
	 SCHEMESUBGROUP,
     LBS,
     RANGESUM(LBS, IF(PREVIOUS(SCHEMESUBGROUP) = SCHEMESUBGROUP, PEEK(CUMULATIVELBS))) AS CUMULATIVELBS
RESIDENT Data
ORDER BY SCHEMESUBGROUP, INSTALLDATE;

DROP TABLE Data;

 

Then for your bar chart:
Dimension: =MONTHNAME(INSTALLDATE) //or your [INSTALLDATE.autoCalendar.YearMonth] field
Dimension: SCHEMESUBGROUP
Expression: MAX(CUMULATIVELBS)
bar_chart.png

View solution in original post

phillipmason
New Contributor

Re: Bar Chart with running total and multiple dimensions

Thank you, that has worked a treat!

 

cheers. Phillip