How to Create calendar measure

[paulyeo11]

Hi All

I have this QVF file , which i like to learn how to Create calendar measure , Hope some one can advise me how to do this ? As i try i keep get error msg " No data field detected....

What i did is ( After view the you tube demo ) :-

Step 1 I click on Field icon , which is lower of 4 icon.

Step 2 I go to search box , enter " sales "

Step 3 I click use mouse right click on " sales " Create calendar measure

Step 4 I see the error msg came out " No data field detected....

Paul Yeo

[paulyeo11]

my QVF ,

[paulyeo11]

Any one can help me ?

[Michael_Tarallo]

Hi Paul - I see you have opened the thread, I missed this.

Check this video out and let me know, please provide detailed steps if this does not work for you:

Creating calendar measures - Qlik Sense - YouTube

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

[paulyeo11]

Hi Michael

I have view the video , and I understand what I should do.

What I do is I using the Qlik Sense Demo App , which I know it should be working , but the fact is when I click on the field icon and right click and select create master calendar , the preview of calendar measure does not appear.

Hope you can use the QS existing demo QVF and show me how you can create a calendar measure . So I am able to know where I go wrong ?

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

[Michael_Tarallo]

Hi Paul - can you please provide me some screen-shots of what you are seeing? You should be able to copy and past them into the discussion box where you type the reply - you can also attach them (.jpg / .png) as image files.

If you choose to attach the screen captures, make sure you put the community discussion editor in "Use advance editor" mode (top right)  - that way the attach link will appear in the bottom right.

[Michael_Tarallo]

Ok are you getting this error - I just tried your app:

You need to have a derived date field (date and associated dimensions) - this is usually interpreted and created by the data manager when you load your tables from source (which automatically inserts the declare and derive statements in the script).

I see you are doing a binary load as well - since those fields are not derived yet, and it is not being loaded by the data manager, it will not know to create the auto-calendar.

You need to have a date field like this in the source data in order for the data manager to create the auto-calendar script.

Then the Create Calendar Measures will show:

See my attached sample .qvf

If using Qlik Sense Desktop please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5. If using Qlik Sense Enterprise Server please import .qvf into your apps using the QMC - Qlik Management Console.

I also attached the source data so you can see how it takes the OrderDate field and breaks it up into date dimensions. Under the covers it is generating this script:

If you want to use your binary load - I WOULD ASSUME you should be able to add this after the load using the date field name you have in your binary loaded data - and replace OrderDate in the syntax below with your date field name.

DERIVE FIELDS FROM FIELDS [OrderDate] USING [autoCalendar] ;

Here is the complete generated script:

[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 [OrderDate] USING [autoCalendar] ;

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

[paulyeo11]

Hi Michael

Step 1 , i use QS demo app , sales discovery

Step 2 , I open the Sheet 1, Performance Dishboard. and i click Edit and Click on Field icon.

Step 3 , I search for field name "Sales Amount" and i right click Sales Amount.

Step 4 I click on Create Calendar Measure. and i stuck here. i cannot create mast items from here.

Hope you can tell me where i go wrong ?

Paul Yeo

[paulyeo11]

Hi Michael

I try using your Demo App.QVF , I am able to get the preview of measure , as below :-

So my issue because of the date field , which i need to create.

As refer to below pic , it is only those field which have Calendar 12 icon then can use for create calendar ?

Paul

[paulyeo11]

Hi Michael

I follow your instruction , i copy and paste the script you provide me , and i only change the OrderDate , to my master calendar date file.

But it still not working,

I do notice that the 2 line of my code [autocalendar]: the charater is not turn into blue color , it is gray color , any thing wrong with that ?