
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar for Period
Hi, I created a master calendar table to map order table. In our business scope. We need calculate period order value. For example: When I select P1, shows from 1st Jan to 30th Jan, when I select P2, shows 1st Jan to the end of February . when I select YTD shows order value from 1st Jan to till now . So I set today as YTD in master calendar script. But when I select YTD. If there is no order today , then other filters could not be selected. how to solve this issue?
Here comes master calendar table script:
Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
if(TempDate=today(),'YTD', 'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1) as FMonth,
if (date(TempDate)>='1/1/2022', 'CY','PY') as FY,
'Q'&Ceil(month(addMonths(TempDate,3)) / 3) as FQ;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
- Tags:
- master calendar
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understand your aim right, you couldn't reach it with this approach because you try to flag periods and YTD within the same field within a classical dimension-table and both requirements of YTD and the periods are opposite against each other. Means if you query date <= today() you could assign YTD properly but now all periods would be empty/null.
This means you will either need two flag-fields for this information or you need to adjust the dimension-table - directly and/or by adding another one - which hasn't a 1:1 relation to the facts else a 1:n which could be resolved with The As-Of Table - Qlik Community - 1466130.
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Try like below
let vMinDate=num(makeDate(2021,10,1));
let vMaxDate=Floor(today());
Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(YearToDate(TempDate),'YTD') as FYTD,
'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1 as Fmonth,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
'Q'&Ceil(month(addMonths(TempDate,3)) / 3) as FQ;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
CrossTable(Flag, FMonth)
LOAD FROZENDATE, FYTD, Fmonth Resident Master_Calendar_Temp;
DROP Field Flag;
exit Script;
use "FMonth" in listbox / filters
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Instead of Today, you can try with vMaxDate for YTD calculation
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You mean that create a variable for YTD? Does filter could show it?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
In place of today(), you can use vMaxDate
if(TempDate=$(vMaxDate),'YTD',
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
let vMinDate=num(Date('2021-10-01'));
let vMaxDate=today();
Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(TempDate=$(vMaxDate),'YTD', 'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1) as FMonth,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
'Q'&Ceil(month(addMonths(TempDate,3)) / 3) as FQ;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
exit Script;
Is it right? It seems not work. If I selected YTD, for 'DI Branch' is also in gray. 'YTD' is still a single day, it is not a period.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understand your aim right, you couldn't reach it with this approach because you try to flag periods and YTD within the same field within a classical dimension-table and both requirements of YTD and the periods are opposite against each other. Means if you query date <= today() you could assign YTD properly but now all periods would be empty/null.
This means you will either need two flag-fields for this information or you need to adjust the dimension-table - directly and/or by adding another one - which hasn't a 1:1 relation to the facts else a 1:n which could be resolved with The As-Of Table - Qlik Community - 1466130.
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Try like below
let vMinDate=num(makeDate(2021,10,1));
let vMaxDate=Floor(today());
Master_Calendar_Temp:
LOAD
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(YearToDate(TempDate),'YTD') as FYTD,
'P'&Month(AddMonths (Date(TempDate,'MM/dd/yyyy'),3))*1 as Fmonth,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
'Q'&Ceil(month(addMonths(TempDate,3)) / 3) as FQ;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
CrossTable(Flag, FMonth)
LOAD FROZENDATE, FYTD, Fmonth Resident Master_Calendar_Temp;
DROP Field Flag;
exit Script;
use "FMonth" in listbox / filters
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@MayilVahanan @marcus_sommer Your reply all can work. I am so appreciated. Thank you so much.
