Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
G3S
Creator III
Creator III

Modifying auto generating calendar

hi,

got a  large dataset which has few different date fields. 

Date bridge or master calendar create too many combinations by filling in the dates. 
trying to create a calendar which:

  • has a single field based on the multiple datefields.
  • covers only the dates in the fact table without filling in the missing dates in the range. 

currently the output with master calendar is the below full table - showing for 12 months. desired output is only three rows with Apr, May & Sep (based on the highlighted dates). Do not want other months showing. 

Appreciate the help.

calendarq.PNG

 

 script

OrderData:
LOAD DISTINCT
OrderNo,
PartNo,
date(floor(RequiredShipDate),'DD/MM/YYYY') as RequiredShipDate,
date(floor(ShipmentShipDate),'DD/MM/YYYY') as ShipmentShipDate,
date(floor(OrderPlacedDate),'DD/MM/YYYY') as OrderPlacedDate,
RangeMin(RequiredShipDate,ShipmentShipDate,OrderPlacedDate) as MinD

;

 

MinMaxDates:
Let vMinDate=Num(MinD);
Let vMaxDate= Num(Today()+120);

Calendar:
Load
Date($(vMinDate)+RowNo()-1) as Date,
Week($(vMinDate)+RowNo()-1) as Week,
Month($(vMinDate)+RowNo()-1) as Month,
Year($(vMinDate)+RowNo()-1) as Year
AutoGenerate
$(vMaxDate)-$(vMinDate) +1

;

Labels (2)
2 Solutions

Accepted Solutions
Mark_Little
Luminary
Luminary

Not seeing the Full code it is hard to suggest.

You would be looking to remove

MinMaxDates:
Let vMinDate=Num(MinD);
Let vMaxDate= Num(Today()+120);

and

AutoGenerate
$(vMaxDate)-$(vMinDate) +1

;

Then generating the Calendar based off your Date fields, as you have three you will need either need a link calendar link table. 

View solution in original post

G3S
Creator III
Creator III
Author

I managed to show only the required months in a straight table  in the front end by creating a dimension that checks if the months from the 3 date fields are a match to the displayed month. then opted to show column if it was a match only. 

yet to work on charting it in to a bar graph.

View solution in original post

3 Replies
Mark_Little
Luminary
Luminary

Not seeing the Full code it is hard to suggest.

You would be looking to remove

MinMaxDates:
Let vMinDate=Num(MinD);
Let vMaxDate= Num(Today()+120);

and

AutoGenerate
$(vMaxDate)-$(vMinDate) +1

;

Then generating the Calendar based off your Date fields, as you have three you will need either need a link calendar link table. 

G3S
Creator III
Creator III
Author

that is the full code. only thing missing is the qvd link where the data is loading from . will check on link table. thank you.

is there a way to limit the months in the calendar load, like a where clause or an if statement? something like 

GeneratedMonth = month(datefield1) or month(datefield2) or month(datefield3)?

G3S
Creator III
Creator III
Author

I managed to show only the required months in a straight table  in the front end by creating a dimension that checks if the months from the 3 date fields are a match to the displayed month. then opted to show column if it was a match only. 

yet to work on charting it in to a bar graph.