Aggr monthly balances per year in Line Chart

[pvanderkolk]

Hi Developers,

In thread Aggr monthly balances per year‌ the formula RangeSum(Above(Count(Amount), 0, Num(Month))), provided by stalwar1‌, works perfectly when sorting on time chronologically.

However, on the sorting tab in Qlik Sense you can't change the order of precedence for the dimensions. I want to visualize my data in a line chart, with the twelve months on the x-axis and a separate line for each year. This means that Month will be the first dimension and Year will be the second dimension (which will give a different order e.g. Jan '16, Jan '17, Feb '16, ....). The formula in the referred thread was based on Year as first dimension and Month as second dimension. So using Above() won't do the trick anymore. Aggregating the formula by year generates the total count per year, but only shows this total in the first month of that year. I need the cumulative count for each month per year.

I've created some sample data below. How should I amend the formula to see the correct line chart?

Thanks,

Pim

Month	Year	Orders
Nov	2015	511
Dec	2015	2001
Jan	2016	1567
Feb	2016	1832
Mar	2016	1905
Apr	2016	1798
May	2016	2019
Jun	2016	2088
Jul	2016	1989
Aug	2016	2101
Sep	2016	2100
Oct	2016	2085
Nov	2016	2120
Dec	2016	2209
Jan	2017	2139
Feb	2017	2250
Mar	2017	2241
Apr	2017	2268
May	2017	2239
Jun	2017	2278
Jul	2017	2325
Aug	2017	2317

[sunny_talwar · Accepted solution]

Try this

Aggr(RangeSum(Above(Count(Amount), 0, Num(Month))), Year, MonthYear)

Aggr() function will take care of your sorting issue here.... If the above doesn't work, then you can try this

Aggr(RangeSum(Above(Count(Amount), 0, Num(Month))), Year, (MonthYear, (NUMERIC)))

Here MonthYear is created in the script like this

MonthName(Date) as MonthYear

or

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear

[pvanderkolk]

Again, thanks a lot Sunny! Bullseye!