YTD in set expression over a calendar year end - need a dynamic solution
Hi Qlik family,
In my KPI reporting I report monthly, quarterly, and annual activity using set analysis. I only import data up to the end of the last complete month cycle, so as of today, 19th June, my imported data is up to the end of May only. The future data import on July 1st, will pull in June and so on.
Right now we are in June, so:
Last month = May
Last Quarter = Jan, Feb, Mar
YTD = Jan, Feb, Mar, Apr, May
I use the following syntax regarding those in the set analysis:
Now it has occurred to me that when this year comes to a close on 31-Dec-2017, and we roll over into January, my YTD syntax might not work in the way I need it to - but I am not 100% sure and hope someone can help me clarify. At this point in my Qlik journey I haven't gone across a calendar year end, so I'm not sure what will happen.
My suspicion is that the QlikSense autoCalendar InYTD syntax will calculate that at any date I do reporting in January, InYTD will want data from Jan 1st onwards, and as I don't load Jan data until Feb 1st, all measures using this InYTD syntax will be blank. This means that Dec becomes the problem in a YTD sense.
This is a bit of a pain, because it means that my automations for reporting get compromised.
So with thinking it needs something a bit like this:
January '18 reporting (looking at YTD Jan-Dec '17) needs to use 'last year to date' in set expression
Feb-Dec '18 reporting (looking at YTD across 2018 for the months of Jan-Nov) needs to use 'InYTD' in set expression
January '19 reporting (looking at YTD Jan-Dec '18) needs to use 'last year to date' in set expression
Feb-Dec '19 reporting (looking at YTD across 2019 for the months of Jan-Nov) needs to use 'InYTD' in set expression
....and so on
....so it feels like I need a dynamic set expression to manage this.
Maybe something like this in the set expression (written properly of course!):
If [MyDate.autoCalendar.Month] = Jan, then use 'last year to date syntax' else use 'InYTD syntax'
Does that sound right? Is there another way of doing this? Or have I not understood the set expression or autoCalendar right?
I need to get this right because I have a lot of measures using YTD, and I can only really trial this over a live year end!