Master calender min and max

[tomgrouwe]

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;

[Anil_Babu_Samineni · Accepted solution]

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;

[tomgrouwe]

Thank you! Seems to work.

One question regarding this:

LOAD MonthStart(Today()) as MinDate,

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

[Anil_Babu_Samineni]

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

MakeDate(2017,10,01) as MinDate

[tomgrouwe]

I see. Thank you for your help!

[Anil_Babu_Samineni]

Always,

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

[tomgrouwe]

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?

[Anil_Babu_Samineni]

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..