Calendar measures, Data load editor and Data Manger

[Anonymous]

This, from "create Calendar Measures":

"Calendar measures do not support calendars created using the Data load editor. If you use calendars created using Data load editor and want to create calendar measures, you must load a table containing a date field using Data manager for use with your tables loaded using Data load editor."

Can anyone please demonstrate (by video or line-by-line instructions) how to accomplish the last clause of the above boxed instructions on this subject matter?  (  Create calendar measures ‒ Qlik Sense    ) 

When I start a new sheet in my current app (Data load Editor product)  and try to add a table using Data Manager, I get an error. 

(incidentally, I would like to appeal to QLIK to take time to standardize processes and procedures so that functionality available with Data Load Manager should also be easily available with Data Load Editor; same for desktop versus cloud-based QLIK. Thanks. AND you are doing a great job thus far!)

[Ivan_Bozov · Accepted solution]

Hi! What is meant by the statement quoted by you is that calendar measures (e.g. Sum Spend YTD) work only with the autoCalendar. Date fields are mapped by default to the autoCalendar when loaded in Data manager. If you use a custom master calendar and map your date fields to it, you won't be able to use the calendar measures. Therefore, there are two things that you can do, both include the use of the autoCalendar:

1. Load the data using the Data manager and specify the date field as Date if it is not automatically recognized. This will create a script with autoCalendar and your date mapped to it.
2. Paste the autoCalendar below in your script and map your date field to it:

[autoCalendar]:
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS YourDateField USING [autoCalendar] ; //Change YourDateFiled with the actual name of your date field

About the other problem - close Qlik and then try again. This happened to me once, no idea why.

[Anonymous]

Thanks: it works!

I just used option # 2: copied it to my script in load editor and substituted the date fields in my table for <YourDateField> in line 26.

That was the only change to make.

I appreciate your help!

[Ivan_Bozov]

Glad it works. Cheers!

[k_robertson]

Hi there,

I had the same problem. I pasted the autocalendar script in the data load editor. I changed the date field to the name in my tables. When I try to insert these fields into my sheets, they produce nothing. They are listed under the date field, but they don't seem to do anything.

Is there something else I should be taking note of when I run the script editor other than changing the field name?

Thanks