Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
tomgrouwe
New Contributor III

Master calender min and max

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;

1 Solution

Accepted Solutions

Re: Master calender min and max

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

7 Replies

Re: Master calender min and max

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

tomgrouwe
New Contributor III

Re: Master calender min and max

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

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

MakeDate(2017,10,01) as MinDate

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tomgrouwe
New Contributor III

Re: Master calender min and max

I see. Thank you for your help!

Re: Master calender min and max

Always,

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tomgrouwe
New Contributor III

Re: Master calender min and max

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)