topic Re: Master calender min and max in QlikView

Master calender min and max

tomgrouwe — Wed, 25 Nov 2020 16:16:04 GMT

Dear experts,

Can you help me out with the following issue?

I've set up a master calender and i want to set the minimal date on 1-10-2017 and maximum date on 30-9-2018. How to do this. Also, When i create a tablechart i can see all the dates the calender created but there is no further data to display in these rows, and i don't want Qlikview to display these rows. How to solve this? Below you'll find the script for the calender.

minmaxdates:

Load

NUM(Date('01/10/2017','DD/MM/YYYY')) As minDate,

Num(Date('30/09/2018','DD/MM/YYYY')) As maxDate

AutoGenerate 1;

Let vMindate = Num(Peek('minDate',0,'minmaxdates'));

Let vMaxdate = Num(Peek('maxDate',0,'minmaxdates'));

DROP table minmaxdates;

DataRanges:

Load

$(vMinDate) + IterNo()-1 as dateNum,

Date($(vMinDate) + IterNo()-1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo()-1 <= $(vMaxDate);

MasterCalender:

Load

TempDate AS AcLoadDate,

Day(TempDate) AS Day,

Week(TempDate) As Week,

Year(TempDate)&'-'&Week(TempDate) As YearWeek,

Month(TempDate) As Month,

Year(TempDate)&'-'&Month(TempDate) As YearMonth,

'Q'& Ceil(Month(TempDate)/3) As Quarter,

Year(TempDate)&'-'&'Q'& Ceil(Month(TempDate)/3) As YearQuarter,

Year(TempDate) As Year

Resident DataRanges Order by TempDate asc;

Drop Table DataRanges;

Re: Master calender min and max

Anil_Babu_Samineni — Tue, 17 Oct 2017 08:03:56 GMT

Try this?

Calendar:

LOAD *,

Day(Date) as Day,

WeekDay(Date) as WeekDay,

Week(Date) as Week,

WeekName(Date) as WeekName,

Month(Date) as Month,

MonthName(Date) as MonthName,

Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

'Q'&Ceil(Month(Date)/3) & '-' & Year(Date) as QuarterYear,

QuarterName(Date) as QuarterName,

Year(Date) as Year,

Day(Date) & '-' & Month(Date) as DayMonth,

'Week'&Ceil(Day(Date)/7) as WeekNum,

WeekYear(Date) as WeekYear;

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD MonthStart(Today()) as MinDate,

MonthEnd(AddMonths(Today(),11)) as MaxDate

AutoGenerate 1;

Re: Master calender min and max

tomgrouwe — Tue, 17 Oct 2017 08:19:41 GMT

Thank you! Seems to work.

One question regarding this:

LOAD MonthStart(Today()) as MinDate,

Is it so, that in November, October would be excluded?

Re: Master calender min and max

Anil_Babu_Samineni — Tue, 17 Oct 2017 08:23:19 GMT

Yes, You can use this for not exclude the October when we are at November

MakeDate(2017,10,01) as MinDate

Re: Master calender min and max

tomgrouwe — Tue, 17 Oct 2017 08:37:26 GMT

I see. Thank you for your help!

Re: Master calender min and max

Anil_Babu_Samineni — Tue, 17 Oct 2017 08:39:22 GMT

Always,

Note - If you like to wish to close this thread and some response mark as helpful from thread. Go ahead and do ...

Re: Master calender min and max

tomgrouwe — Wed, 18 Oct 2017 09:00:53 GMT

Dear experts,

However, this worked. I now have the following issue:

I need to make a scorecard with detailed data from the source (XLS file).

However, if I create a table chart, I get to see more lines than orginally created in the XLS file because they are filled with the dates that are created. I only want to see the lines that are actually filled with data and not only with generated dates. How to do this?

Re: Master calender min and max

Anil_Babu_Samineni — Wed, 18 Oct 2017 09:19:40 GMT

I would ask you to create one more thread for that. You can use Calendar table to Fact table using Concatenate between to pull all records from Calendar and your XLS table..