topic Bar Chart with running total and multiple dimensions in App Development

Bar Chart with running total and multiple dimensions

phillipmason — Mon, 20 Dec 2021 21:38:40 GMT

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

Re: Bar Chart with running total and multiple dimensions

Nicole-Smith — Tue, 14 Jan 2020 16:09:43 GMT

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)

Re: Bar Chart with running total and multiple dimensions

phillipmason — Wed, 15 Jan 2020 09:31:16 GMT

Thank you, that has worked a treat!

cheers. Phillip