# 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
Did you mean:
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
MVP

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)``
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.

MVP

The expression will still stay the same

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?

MVP

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

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), Day_Fault_Received)``