Master Calendar for Period

[StacyCui]

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

[MayilVahanan]

Hi

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

[StacyCui]

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', 

[StacyCui]

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.

[marcus_sommer · Accepted solution]

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 · Accepted solution]

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

[StacyCui]

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