Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pvanderkolk
New 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
MVP
MVP

Re: Aggr monthly balances per year in Line Chart

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

2 Replies
MVP
MVP

Re: Aggr monthly balances per year in Line Chart

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
New Contributor III

Re: Aggr monthly balances per year in Line Chart

Again, thanks a lot Sunny! Bullseye!

Community Browser