YTD in set expression over a calendar year end - need a dynamic solution

[gileswalker]

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:

Last Month

[MyDate.autoCalendar.MonthsAgo]={1}

Last Quarter

[MyDate.autoCalendar.QuartersAgo]={1} ,[MyDate.autoCalendar.YearsAgo]={0}

YTD

[MyDate.autoCalendar.InYTD]={1} ,[MyDate.autoCalendar.YearsAgo]={0}

This works perfectly.

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!

Thanks

Giles

[gileswalker]

After some research, I think I may have found the solution I'm looking for:

If(Year(Today())>Year(Monthstart(AddMonths(Today(),-1))),

Sum( { $< [MyDate.autoCalendar.YearsAgo]={1} > } [Sales] )

,

Sum( { $< [MyDate.autoCalendar.YearsAgo]={0}> } [Sales] ))

So in laymans terms, if the [Year of today] is greater than the [Year of last month] then:

• sum sales limited to all of Last Year,
• else sum sales limited to all of this year.

Not sure if I will have any performance issues with this, and maybe there is good cause to use variables......if anyone has any ideas or comments on that, I'd appreciate it.

Thanks

Giles

[gharvey4]

Hey Giles, did this work for you? I have the same problem. I am thinking about trying to solve the problem by changing the autocalendar to change 'Yearsago' to start in February, but I'm not sure how to do that...yet

George