Year on Year comparison per date

[nsm1234567]

Hey there,

I was recently asked to review some code I wrote a few years back to handle a user request and which one of our developers didn't understand.  The scenario is pretty straightforward.  You have a straight table with sales figures and you want to compare these year on year by date.  So for 2019/01/01 you want to compare to 2018/01/01 in this example.  The "wrong" result would be the image below, where the numbers sit on different rows and so you can't do a simple % difference check.

The way I handled this previously was to create a "trailing table."  In the attached, I've solved this problem in three different ways, but was thinking there should be other ways of dealing with this?  Was wondering how those in the community would solve this and if there isn't some magic pure aggr() or set analysis type solution without the above() function that could be used to handle cases like this?  I also am not totally happy with the "above" method I used in the app as I had to create a variable to do the offset which I'm sure could cause issues.

[Gysbert_Wassenaar]

 See this document for a discussion of the issue: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...

Here's another one: https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927

[nsm1234567]

Hey there,

I don't think these examples are 100% what I'm looking for here (HIC has an "As Of" example, but that was one of my methods in the attached app).  So if you look at the "Option 1" in my app as an example, the below expression works in that it generates the correct result but it's not very robust as say there were no sales on 2019/01/02 it'd then "break" my dynamic offset (the $(vAboveCount) variable).  It just seems as though there should be a more robust way to do this using an expression.

=sum(aggr(Above(Sum(Sales),$(vAboveCount)),(Date,(NUMERIC))))

[Gysbert_Wassenaar]

In my experience there isn't. The As-Of table is the best solution imho. And if your data is missing dates then you should generate the missing dates in a master calendar and link the As-Of table to the master calendar.

[sunny_talwar]

I second that!!

[nsm1234567]

Thanks for the feedback.  I agree that in a vacuum the "As Of" table is the best solution.  I've found though that at times it's difficult to implement in a large/old existing application as you have to change a large number of expressions and dimensions and it then becomes quite a large project to amend all of them to work with the new "As Of" table (why I've previously used a "trailing table").  It just "feels" as though there should be a more elegant expression-based solution, but you're right, it seems that there isn't.