Announcements
cancel
Showing results for
Did you mean:
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:
Date(TempDate) as "FROZENDATE",
if (date(TempDate)>='1/1/2022', 'CY','PY') as FY,
\$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While (\$(vMinDate) + IterNo() - 1) <= \$(vMaxDate);

2 Solutions

Accepted Solutions

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

MVP

Hi

Try like below

let vMinDate=num(makeDate(2021,10,1));
let vMaxDate=Floor(today());

Master_Calendar_Temp:
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(YearToDate(TempDate),'YTD') as FYTD,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
\$(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
7 Replies
MVP

Hi

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

Thanks & Regards, Mayil Vahanan R
Creator
Author

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

MVP

Hi

In place of today(), you can use vMaxDate

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

Thanks & Regards, Mayil Vahanan R
Creator
Author

let vMinDate=num(Date('2021-10-01'));
let vMaxDate=today();

Master_Calendar_Temp:
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
\$(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.

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

MVP

Hi

Try like below

let vMinDate=num(makeDate(2021,10,1));
let vMaxDate=Floor(today());

Master_Calendar_Temp:
Date(TempDate) as "FROZENDATE",
Day(TempDate) as 日,
Num(Month(TempDate)) as 月,
if(YearToDate(TempDate),'YTD') as FYTD,
Month(TempDate) as 月份,
Year(TempDate) as 年,
if (date(TempDate)>='10/1/2022', 'CY','PY') as FY,
\$(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