Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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

8 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.