- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! Seems to work.
One question regarding this:
LOAD MonthStart(Today()) as MinDate,
Is it so, that in November, October would be excluded?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, You can use this for not exclude the October when we are at November
MakeDate(2017,10,01) as MinDate
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I see. Thank you for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Always,
Note - If you like to wish to close this thread and some response mark as helpful from thread. Go ahead and do ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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..