Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

paulyeo11
Valued Contributor II

How to Create calendar measure

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

1 Solution

Accepted Solutions
paulyeo11
Valued Contributor II

Re: How to Create calendar measure

Hi Michael

In my QVW file the master calendar script as below , So i should use TempDate instead of date right ? But i just try it should not work. By the way your app1.QVF , i also cannot find the field name with Calendar 12 icon , But for your app2.qvf i found the OrderDate field , have the Calendar 12 icon.

MasterCalendar:

LOAD TempDate AS date,

  D AS link_Date,

  D AS Date,

  day([TempDate]) as [day],

  day(D) AS Day,

//////////////////////////////////// Week

// Week([TempDate]) as Week,

  week(D) AS Week,

  weekday(D + 2) AS Weekday,

  dual(week([TempDate])&'-'&weekyear([TempDate]),weekstart([TempDate])) as YearWeek,

12 Replies
paulyeo11
Valued Contributor II

Re: How to Create calendar measure

my QVF ,

paulyeo11
Valued Contributor II

Re: How to Create calendar measure

Any one can help me ?

Employee
Employee

Re: How to Create calendar measure

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

Regards,
Mike Tarallo
Qlik
paulyeo11
Valued Contributor II

Re: How to Create calendar measure

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>

Employee
Employee

Re: How to Create calendar measure

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.

Regards,
Mike Tarallo
Qlik
Employee
Employee

Re: How to Create calendar measure

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

Regards,
Mike Tarallo
Qlik
paulyeo11
Valued Contributor II

Re: How to Create calendar measure

Hi Michael

Step 1 , i use QS demo app , sales discovery

SD1.png

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

SD2.png

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

SD3.png

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

SD4.png

Hope you can tell me where i go wrong ?

Paul Yeo

paulyeo11
Valued Contributor II

Re: How to Create calendar measure

Hi Michael

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

SD_F.png

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 ?

SD8.png

Paul

paulyeo11
Valued Contributor II

Re: How to Create calendar measure

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 ?

SD9.png

Community Browser