Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
pvanderkolk
Partner - Contributor III
Partner - Contributor III

Aggr monthly balances per year in Line Chart

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

 

MonthYearOrders
Nov2015511
Dec20152001
Jan20161567
Feb20161832
Mar20161905
Apr20161798
May20162019
Jun20162088
Jul20161989
Aug20162101
Sep20162100
Oct20162085
Nov20162120
Dec20162209
Jan20172139
Feb20172250
Mar20172241
Apr20172268
May20172239
Jun20172278
Jul20172325
Aug20172317
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Partner - Contributor III
Partner - Contributor III
Author

Again, thanks a lot Sunny! Bullseye!