Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bobi
Partner - Contributor II
Partner - Contributor II

Add last 3 months Avg to next month

Hi. I have a line graph and the dimension is YearMonth.

In that graph I want to have a line where I show actuals until current month. And after that I want to add an average based on the 3 last months that are completed (right now: december, november, october).

The line should show the accumulated value.

Lets say we have values for Yearmonths like this:

2021-01: value 25

2020-12, value: 3 

2020-11, value: 10

2020-10, value: 5

 

Then avg for 3 months should be: (3+10+5)/3 = 6

 

In yearmonth-graph then should we have actual value: 25 on 2021-01. On 2021-02 it shows accumulated: 25+6 = 31

In march: 25+6+6=37 and so on. So the Avg of the last 3 finished months should be added to each future month of the year.

 

Any ideas? I can get the value but then it is just added 3 months back and not in future. Tried with AsOfMonths-tables and so on without succeding so far.

Labels (2)
1 Reply
Channa
Specialist III
Specialist III

add average value

=avg({<Date={"<=$(=MonthStart((vDate),-2))"}>}Value)

 

vDate=Max(date)

Channa