topic Re: How to Create calendar measure in App Development

How to Create calendar measure

paulyeo11 — Wed, 05 Apr 2017 10:20:50 GMT

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

Re: How to Create calendar measure

paulyeo11 — Wed, 05 Apr 2017 10:22:33 GMT

my QVF ,

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 05:48:00 GMT

Any one can help me ?

Re: How to Create calendar measure

Michael_Tarallo — Thu, 06 Apr 2017 11:38:58 GMT

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

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 11:54:52 GMT

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>

Re: How to Create calendar measure

Michael_Tarallo — Thu, 06 Apr 2017 12:20:35 GMT

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.

Re: How to Create calendar measure

Michael_Tarallo — Thu, 06 Apr 2017 12:33:05 GMT

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] ;

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 12:37:27 GMT

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

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 13:13:34 GMT

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

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 13:33:45 GMT

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 ?

Re: How to Create calendar measure

Michael_Tarallo — Thu, 06 Apr 2017 14:35:19 GMT

Hi Paul -

It works for me - see attached apps - App2 - binary loads from App1 - and then uses this code:

Binary 'lib://DataFolder/app1.qvf';


    [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] ;

I think the problem might be with the "date" field name - date is a keyword, I don't think you should use that name, you might need to change it.

Note my field name grabbing data from app1 is OrderDate

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 19:38:27 GMT

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,

Re: How to Create calendar measure

paulyeo11 — Thu, 06 Apr 2017 22:22:31 GMT

Hi Michael

Now it work. I need to go to my load script on sales order table add a field name OrderDate. I can not use the master island calendar Date field , it will not work.

Thank you very much for you help.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

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

Re: How to Create calendar measure

johndoub — Fri, 13 Dec 2019 16:51:39 GMT

can i create a calendar measure for sum(sales) for today's date?

Re: How to Create calendar measure

johndoub — Fri, 13 Dec 2019 21:07:02 GMT

i am trying to create a calendar measure for my date field BUS_DT which will sum the sales for TODAY.

is that possible?

john doub