Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
StacyCui
Creator
Creator

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

StacyCui_0-1669006977462.png

StacyCui_1-1669007040309.png

 

2 Solutions

Accepted Solutions
marcus_sommer

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 

View solution in original post

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

7 Replies
MayilVahanan

Hi

Instead of Today, you can try with vMaxDate for YTD calculation

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
StacyCui
Creator
Creator
Author

You mean that create a variable for YTD?  Does  filter could show it?

MayilVahanan

Hi

In place of today(), you can use vMaxDate

if(TempDate=$(vMaxDate),'YTD', 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
StacyCui
Creator
Creator
Author

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.

StacyCui_1-1669012253796.png

 

marcus_sommer

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 

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
StacyCui
Creator
Creator
Author

@MayilVahanan @marcus_sommer Your reply all can work. I am so appreciated. Thank you so much.