Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

quriouss
Contributor III

Group Dates into YTD, YTD-1, YTD -2, etc.

I have a bunch of measures in rows, and I want to do a YTD comparison for the past X years (in the columns).

I have a master calendar set up so I can ask the user to select months and dates.  Is that the place to also set up a dimension with the members "Current YTD" "YTD-1" "YTD-2" etc.?

I can then ask the user to select the periods she wants to see in a filter.  This will also avoid using the Pivot Table object.

Does this make sense or is there a better way to do this?  (One way would be in a load script but that would test every date and see where it fits so it wouldn't benefit directly from the associative model.)

1 Solution

Accepted Solutions
quriouss
Contributor III

Re: Group Dates into YTD, YTD-1, YTD -2, etc.

I think I've answered my own question.  In my Master Calendar I added a function to test each date to see if it's in the Current YTD or not. It needs a bit more refinement but I think it answers my needs;

MasterCalendar: 

Load 

  TempDate AS "Sale Date", 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month,

  IF(inyeartodate(TempDate, today() ,0),'YTD','notYTD') as YTD,

  Day(TempDate) As Day, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

1 Reply
quriouss
Contributor III

Re: Group Dates into YTD, YTD-1, YTD -2, etc.

I think I've answered my own question.  In my Master Calendar I added a function to test each date to see if it's in the Current YTD or not. It needs a bit more refinement but I think it answers my needs;

MasterCalendar: 

Load 

  TempDate AS "Sale Date", 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month,

  IF(inyeartodate(TempDate, today() ,0),'YTD','notYTD') as YTD,

  Day(TempDate) As Day, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar