Master Calendar/Set Expression - YTD Calc Problem

[gileswalker]

Hi Qlik Community

Hope someone can help guide on this please?

I am using the new function in Sense 3.2 for creating master calendar items - it's really great, and so far, so good.  However I have come to the limit of my set expression knowledge with one particular calculation.  Here goes......

Objective: I have to create time based KPIs based on an ETA date (ETA is actually the estimated date of arrival).  I have to report prior periods for comparison purposes, ie May-16 v May-17, Q1-16 v Q1-17 and so on.

The data is imported to Qlik so that I only retrieve data from 01-Jan-16 to the last 'complete' month .....example, today is 14-Jun-17, and the month of June is still in progress, and so I don't want Jun-17 data yet......so Qlik only imports Jan-16 to May-17.

From here, when I use terms like last month, I am talking literally......so given today is 14-Jun-17, last month is May-17 and last quarter is Q1 (Jan, Feb, Mar).  Current month is the month we are in, so given today is 14-Jun-17, current month is Jun-17.  Quite straight forward, I hope that makes sense?

With the way Sense 3.2 works, the set expressions the 'create master calendar items' has created for me are as follows:

Last month versus the same month year prior:

Last month uses: [ETA.autoCalendar.MonthsAgo]={1}, [ETA.autoCalendar.YearsAgo]={0}

Last month, prior year uses: [ETA.autoCalendar.MonthRelNo]={1}, [ETA.autoCalendar.YearsAgo]={1}

Last quarter versus the same quarter year prior:

Last quarter uses: [ETA.autoCalendar.QuartersAgo]={1} ,[ETA.autoCalendar.YearsAgo]={0}

Last quarter, prior year uses: [ETA.autoCalendar.QuarterRelNo]={1}, [ETA.autoCalendar.YearsAgo]={1}

However, when it comes to the creation of the comparative YTD (year to date) measure I come across a problem as I shall explain.  The syntax I have used is this:

Current YTD (2017): [ETA.autoCalendar.InYTD]={1} ,[ETA.autoCalendar.YearsAgo]={0}

Prior YTD (2016): [ETA.autoCalendar.InYTD]={1} ,[ETA.autoCalendar.YearsAgo]={1}

But given the historical data available, and the syntax used, it produces this chart:

So the problem is that June 2016 has data, but June 2017 data is purposefully not being imported yet as June 2017 is not complete.  This causes the single "Prior Year" bar to be present in the chart for "June", which I don't want.

I need to know how to code the "Prior YTD" so that it ignores 'current month last year', current month being June in this case.

I am sure this is a simple fix, but my skills are still developing on this type of topic so I need some assistance please.

Thanks in advance

Giles

[dan_sullivan]

I would change your autocalendar definitions to suite what you need.  Here are some examples:

LET vToday = num(Today());

LET vYesterday = IF(WEEKDAY(Today())='Mon',Today()-3,IF(WEEKDAY(Today())='Sun',Today()-2,Today()-1)); // On Mon,Sun,Sat yesteday should be friday

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

Date ($1) AS [Date],

  WeekDay($1) AS [Weekday],

  Month($1) AS [Month],

  Date(monthstart($1), 'YYYY-MMM') AS [YearMonth],

  Year($1) AS [Year],

  Week($1) AS [Week],

  IF ( num($1) = num($(vToday)), 1, 0 ) AS [Today Flag],

  IF ( num($1) = num($(vYesterday)), 1, 0 ) AS [Yesterday Flag],

  IF(Month($1)=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday)),1, 0) AS [CM Flag],

  IF(Month($1)=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday)) AND $1 <= $(vYesterday),1, 0) AS [CMTD Flag],

  IF(Month($1)<=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday)),1, 0) AS [CY Flag],

  IF(Month($1)<=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday)) AND $1 <= $(vYesterday),1, 0) AS [CYTD Flag],

  IF(Year($1)=Year($(vYesterday)),1, 0) AS [CY Full Flag],

  IF(Month($1)=Month( $(vYesterday))-1 AND Year($1)=Year($(vYesterday)),1, 0) AS [PM Flag],

  IF(Month($1)=Month( $(vYesterday))-1 AND Year($1)=Year($(vYesterday))-1,1, 0) AS [PYM Flag],

  IF(Month($1)<=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday))-1 AND $1 <= $(vYesterday),1, 0) AS [PY Flag],

  IF(Month($1)<=Month( $(vYesterday)) AND Year($1)=Year($(vYesterday))-1 AND $1 <= $(vYesterday),1, 0) AS [PYTD Flag],

  IF(Year($1)=Year($(vYesterday))-1,1, 0) AS [PY Full Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-3) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 3MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-6) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 6MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-9) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 9MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-12) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 12MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-13) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 13MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-19) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 19MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-24) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 24MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-36) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 36MO Flag],

  IF(($1) >= AddMonths(MonthStart($(vYesterday)),-60) AND $1 <= AddMonths(MonthEnd($(vYesterday)),-1),1,0) AS [Prior 60MO Flag],

  IF(($1) >= WeekStart($(vYesterday)) AND $1 <= $(vYesterday),1,0) AS [Current WK Flag],

  IF(($1) >= WeekStart($(vYesterday)-7) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior WK Flag],

  IF(($1) >= WeekStart($(vYesterday)-14) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior 2WK Flag],

  IF(($1) >= WeekStart($(vYesterday)-28) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior 4WK Flag],

  IF(($1) >= WeekStart($(vYesterday)-84) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior 12WK Flag],

  IF(($1)>= WeekStart($(vYesterday)-168) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior 24WK Flag],

  IF(($1) >= WeekStart($(vYesterday)-364) AND $1 <= Weekend($(vYesterday))-7,1,0) AS [Prior 52WK Flag],

DERIVE FIELDS FROM FIELDS [Your Date field Name] USING [autoCalendar] ;