Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Master Calendar/Slider/Calendar Object/Date Range

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Master Calendar/Slider/Calendar Object/Date Range

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


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Master Calendar/Slider/Calendar Object/Date Range

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


talk is cheap, supply exceeds demand
Not applicable

Re: Master Calendar/Slider/Calendar Object/Date Range

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