Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
The expression will still stay the same
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?
Avg is used to decide what data we display (using the set analysis). Are you looking to see how it looks?
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)
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)
@sunny_talwar thanks for the code. Unfortunately it's not working for me :s. I really appreciate your efforts and help.
@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?