topic Prior Year Set Analysis in QlikView

Prior Year Set Analysis

sterno14 — Wed, 25 Nov 2020 16:16:04 GMT

We have a field which I'll call "File Date" (ex. 7/31/2017,6/30/2017,5/31/2017, etc) for ease of explanation. We allow the user to select which "File Date" they want to see their data represented. This field values can go back several years based on the client. What I want to do is take the Max(File Date) and also show the data for all previous File Dates for the same Month & Day but all previous years. For instance if the Max(File Date) is 7/31/2017 I want to show 7/31/2017, 7/31/2016, 7/31/2015 back to the earliest 7/31/YYYY for which I have data in a chart. Can I do this with a set analysis statement?

Re: Prior Year Set Analysis

sunny_talwar — Mon, 28 Aug 2017 16:45:53 GMT

Are you using Year, date, MonthYear as your dimension?

Re: Prior Year Set Analysis

sterno14 — Mon, 28 Aug 2017 18:01:46 GMT

I have File Date as a dimension as I wanted to show the File Date in the chart. I do have YearMonth (FileDate_YearMonth) in my calendar.

Re: Prior Year Set Analysis

sunny_talwar — Mon, 28 Aug 2017 18:29:00 GMT

May be try this

Sum({<FileDate = {"$(=Date(Max(Date)))", "$(=Date(AddYears(Max(Date), -1)))", "$(=Date(AddYears(Max(Date), -2)))", "$(=Date(AddYears(Max(Date), -3)))"}>}Measure)

Re: Prior Year Set Analysis

sterno14 — Mon, 28 Aug 2017 19:54:00 GMT

Thanks. That would work however I do not know how many years to go back since different clients may have different File Date periods. So after looking at your expression what I did is add File Date Month to my calendar and used an expression: {<[File Date Month]={'$(=Month(Max([File Date])))'}. That now gets me what I want. Thanks for all your help.

Re: Prior Year Set Analysis

sunny_talwar — Mon, 28 Aug 2017 19:55:35 GMT

Sounds great