Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted

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
Highlighted

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

Highlighted
Contributor III
Contributor III

Thank you! Seems to work.

One question regarding this:

LOAD MonthStart(Today()) as MinDate,


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

Highlighted

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)
Highlighted
Contributor III
Contributor III

I see. Thank you for your help!

Highlighted

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)
Highlighted
Contributor III
Contributor III

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?

Highlighted

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)