Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
G3S
Creator
Creator

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
mark6505
Partner - Master
Partner - Master

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
Creator
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
mark6505
Partner - Master
Partner - Master

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