Declare and Derive functions for Master Calendar

[beck_bakytbek]

Hi Folks,

i have this example-data to generate the master calendar:

OrgSales:

load * inline [

InvoiceDate, Product, OrderNo, Sales

01.01.2013, Chains, 101, 5500

08.02.2014, Seats, 101, 4800

03.03.2014, Brake Oil, 102, 6500

09.05.2018, Helmets, 104, 4500

];

Calendar:

Declare Field Definition Tagged '$date'

Parameters

first_month_of_year = 1    // OR  first_month_of_year = 3

Fields

Year($1) as Year Tagged '$year',

Month($1) as Month Tagged '$month',

Date($1) as Date Tagged '$date',

Week($1,first_month_of_year) as Week Tagged '$week'

Groups

Year, Month, Date type collection as YearMonthDate;

Derive Fields from Fields InvoiceDate Using Calendar;

everything does work great, but i have a question: the row:  first_month_of_year = 1 // OR  first_month_of_year = 3,

what does it mean? and which consequences do i have if i choose 1 or 3.

Thanks a lot

Beck

[jaumecf23 · Accepted solution]

I see that the variable first_month_of_year is used in the Week function. The variable is used to define the first week day : Integer that defines which day to use as the first day of the week. By default, QlikView functions use Monday as the first day of the week. The following values can be used:

• 0 (= Monday)
• 1 (= Tuesday)
• 2 (= Wednesday)
• 3 (= Thursday)
• 4 (= Friday)
• 5 (= Saturday)
• 6 (= Sunday)

[beck_bakytbek]

Hi Jaume,

first of all thanks a lot for your help and time,

as i understand it, that these numbers in the row respresent the days, so i have a question, how can i use the function day () to show that behind 0 is Monday, and behind 4 is Friday?

Thanks a lot

Beck

[jaumecf23]

I don't know if I understant what exactly you need but you can use this function : Weekday($1).

You can find more information in the following link : weekday ‒ QlikView

[beck_bakytbek]

Thanks a lot for your help  Jaume

[JustinDallas]

May I ask what you are trying to accomplish using Derived Fields?  I had never seen them before your post, and I'm having trouble of thinking of a scenario whereby Derived Fields saves the day.

[beck_bakytbek]

Hi Justin,

as a matter of course,

as you know, the defining a master calendar in Qlik Sense is a common requirement and it is possible to make with such function as: Time and Date functions. With Declare and Derive function is much easier to create the Calendar.

Additional Information to this issue:Creating calendar measures - Qlik Sense - YouTube

if you have any question, let me know

i hope that helps

Beck