# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

 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
Tags (2)
1 Solution

Accepted Solutions
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

## 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

New Contributor III

## Re: Aggr monthly balances per year in Line Chart

Again, thanks a lot Sunny! Bullseye!