Bar Chart with running total and multiple dimensions

[phillipmason]

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 

I need 

Is this possible?

thank you . Phillip

[Nicole-Smith · Accepted solution]

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)

[phillipmason]

Thank you, that has worked a treat!

cheers. Phillip