Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Running 12 Month Average ( Line Chart ) over Last 12 Month

Hello Everyone,

I've got a fairly simple one Today (I think). I have a line chart where I need to show Average [Payment Days] for last 12 Periods.

Chart Dimension is Period, which essentially is MonthName(DateField)

I use this expression in my Set Analysis:

RangeAvg(Above( Avg( {<[Code Group] = {'Sale'}, [grpcode Group] = {'3rd Party'}>} [Payment Days] ), 0, 12)) * Avg(1)image.png

And this is the result that I am getting.

First of all, here I am not quite sure if the expression is actually calculating the Average [Payment Days] over the last 12 Periods. Secondly, I want to limit the results in the chart to only last 12 Periods e.g. Mar 2018 to Feb 2019. 

At the same time I want to be sure that the result for Mar 2018 in this chart is indeed the Average [Payment Days]   >=Apr 2017<=Mar 2018.

Has anybody got any suggestions please?

 

Thanks,

Vlad

3 Replies
Highlighted
Specialist II
Specialist II

Re: Running 12 Month Average ( Line Chart ) over Last 12 Month

If you want to show by month the average?

Avg({<[Code Group] = {'Sale'}, [grpcode Group] = {'3rd Party'},MonthsFromCurrrent={"=<13}>} Aggr(Avg({<[Code Group] = {'Sale'}, [grpcode Group] = {'3rd Party'},MonthsFromCurrrent={"=<13}>}[PaymentDays]),[Period],[Year]))

Add to your calendar, MonthsFromCurrent

Round((num(monthstart(Today(1))-num(monthstart(Date))))/30) AS MonthsFromCurrent,
Highlighted
Contributor III
Contributor III

Re: Running 12 Month Average ( Line Chart ) over Last 12 Month

@dwforest, unfortunately that doesn't work. And I am pretty sure that is not what I am trying to do. You have Avg with set analysis and nested Aggr(Avg()) in it. I don't believe this is right
Highlighted
Contributor III
Contributor III

Re: Running 12 Month Average ( Line Chart ) over Last 12 Month

@sunny_talwar Hi Sunny, do you have any ideas ?