Master Calendar/Slider/Calendar Object/Date Range

[#1]

Hello Qlik Experts,

I will need to create a a date range on my dashboard. The purpose is to show daily view. However, I am really confuse about how to create it. Show I create a Master Calendar in script? Or I can do the same thing as it shows in Josh's video Selecting Arbitrary Date Ranges?

If for my case, I will need to create a Master Calendar, I found different script code from other's posts shows as below, But I don't really understand the code in script. I really want to understand the code and how it work, not just copy & paste the code in my script. For example: why set num(date) value to a variable, what does " date($(vMinDate) + rowno() - 1) " mean etc. Can any one kindly spend some of your time to explain to me more about the script?

Script Example 1:

LET vMinDate = num('01/01/1990');
LET vMaxDate = num(today());

DateIsland:
LOAD
date($(vMinDate) + rowno() - 1) AS D,
year($(vMinDate) + rowno() - 1) AS Y,
month($(vMinDate) + rowno() - 1) AS M,
date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE vMaxDate - vMinDate + 1;

Script Example 2:

DateRange:
LOAD StartDate,
EndDate
FROM

(ooxml, embedded labels, table is Sheet2);

LET varMinDate = num(Peek('StartDate',-1,'DateRange'));
LET varMaxDate = num(Peek('EndDate',-1,'DateRange'));

drop table DateRange;

Calender:
LOAD
num($(varMinDate)+rowno()-1) AS DateNumber,
date($(varMinDate)+ rowno() -1, 'MMMDD') AS TempDate
AUTOGENERATE
$(varMaxDate)-$(varMinDate)+1;

Script Example 3:

Sales:
LOAD
Chr(64 + Ceil(Rand() * 5)) AS ID, // Company ID
Ceil(Rand() * 7) * 1000 AS SalesAmount, // Amount
Date(Ceil(Rand() * 1460) + Date('01/01/2007')) AS CalendarDate // Date starting Jan 1, 2007
AUTOGENERATE 3000;

CalendarTemp:
LOAD
Max(CalendarDate) AS DateMax,
Min(CalendarDate) AS DateMin
RESIDENT Sales;

LET vMaxDate = FieldValue('DateMax', 1);
LET vMinDate = FieldValue('DateMin', 1) -1; // Needed to control the minum date autogenerated in Sales table is included in the MasterCalendar

DROP TABLE CalendarTemp;

MasterCalendar:
LOAD
Date(IterNo() + Date($(vMinDate))) AS CalendarDate,
Week(Date(IterNo() + Date($(vMinDate)))) AS CalendarWeek,
Month(Date(IterNo() + Date($(vMinDate)))) AS CalendarMonth,
Year(Date(IterNo() + Date($(vMinDate)))) AS CalendarYear,
InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), 0) AS YTDFlag,
InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) AS LYFlag
AUTOGENERATE 1 WHILE Date(IterNo() + Date($(vMinDate))) <= Date($(vMaxDate));

LET vYearMax = Year($(vMaxDate));
LET vYearMin = Year($(vMinDate));

Please let me know if I need to create an attach a sample QVW here.

Thanks a lot,

Becky

[Gysbert_Wassenaar · Accepted solution]

What you should really do is pick it apart and see what each part does. If you don't know what date($(vMinDate) + rowno() - 1)  means then pick it apart:

• date(..something...)  -- what does the date() function do?
• $(...something...) -- what is this $(...) thing?
• rowno() -- what does the rowno() function do?
  

You can look in the help file to see what the functions do.

$(...) is a bit harder to find. See here for more information about dollar expansion and variables: The Magic of Variables

Once you know what the parts do you'll be able to understand what the complete expression does.

To generate a calendar a loop is often used. See this blog post: The Master Calendar. And for more information about the 'while' kind of loop see Loops in the Script‌.

Any questions? First try to find a blog post by Henric Cronström‌‌‌ about the subject

[#3]

Thanks GD.

I got my code working before seeing your response yesterday. This post Understanding the Master Calendar (video) helped me to understand each block of code.

I appreciate the time you spent for the response and providing details to me. I checked each part yesterday but didn't understand totally. But the posts you mentioned above helped a lot with understanding script for master calendar, $ sign, etc. So I made your response as the Correct Answer.

After I saw the post from Henric Cronström, my solution of setting my master calender is hard-code the date range, then fill in the missing dates by using autogenerate statement, which shows as below.

LET vStartDate = num('01/01/1990');
LET vEndDate = num(today());

TempCalendar:
LOAD
Date($(vStartDate) + RecNo()) as TempDate autogenerate $(vEndDate) - $(vStartDate);