Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alex-wb
Contributor III
Contributor III

Cumulative months in a rolling period

Hi all,

I am looking to do a current  RYTD based on 1-12 months view comparison vs the previous 12 month before that 13-24 month. However, ever month will be cumulative and backed by the preceding 12 months of data. So for example, month one is May 2021 and is backed by May 2021- April 2020 data, and April 2021 is back by April 2021- March 2020 data and so on so forth. These dates will all shift on a monthly basis as the we enter a new month. I have attached an excel document show what I am trying to achieve. 

The data below is made up data but will show what I am trying to achieve. 

Any help is much appreciated.

Thanks,

Alex

9 Replies
sunny_talwar

Try like this

Previous RYTD

Aggr(RangeSum(Above(Sum(R), 0, 12)) * Avg({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -23)) & '<=' & Date(AddMonths(Max(Date), -12)))"}>}1), Date)

 Current RYTD

Aggr(RangeSum(Above(Sum(R), 0, 12)) * Avg({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -11)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}1), Date)
alex-wb
Contributor III
Contributor III
Author

Hi @sunny_talwar , thanks for answering the question. I just realised I have posted in the wrong group. I meant to post on the Qlik Sense group, if you know the solution to my question but for Qlik Sense I would really appreciate it.

sunny_talwar

The expression will still stay the same

alex-wb
Contributor III
Contributor III
Author

Thanks @sunny_talwar . If you're able to send the QVW as a QVD I'd really appreciate it? Out of interest why are we averaging part of the equation? 

 

 

 

sunny_talwar

Avg is used to decide what data we display (using the set analysis). Are you looking to see how it looks? 

sunny_talwar_0-1624021772898.png

 

alex-wb
Contributor III
Contributor III
Author

Hi @sunny_talwar that does exactly what I need it to do, thanks :).  In the formula you have given, what parts do I need to change with my data fields to make it work.  For example I am counting faults codes, I have changed it to look like this but it doesn't work. I have also change the sum part to a count since it's counting a code and not a numeric number.  

Aggr(RangeSum(Above(count(FAULT_REF), 0, 12)) * Avg({<Month(FAULT_RECEIVED_DAY) = {"$(='>=' & Date(AddMonths(Max(Day_Fault_Received), -11)) & '<=' & Date(AddMonths(Max(Day_Fault_Received), 0)))"}>}1), Date)

 

sunny_talwar

May be try this

Aggr(RangeSum(Above(count(FAULT_REF), 0, 12)) * Avg({<Month(FAULT_RECEIVED_DAY) = {"$(='>=' & Date(AddMonths(Max(Day_Fault_Received), -11)) & '<=' & Date(AddMonths(Max(Day_Fault_Received), 0)))"}>}1), Day_Fault_Received)
alex-wb
Contributor III
Contributor III
Author

@sunny_talwar  thanks for the code. Unfortunately it's not working for me :s. I really appreciate your efforts and help. 

alex-wb
Contributor III
Contributor III
Author

@sunny_talwar  Hi Sunny, I am still trying to work this out and would appreciate your help. Below is how it looks in Qlik for me. Could this be something to do with the formatting of my dates? 

alexwb_0-1628500123805.png