Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jamiemcc
Contributor III
Contributor III

How to tie in My billing Month with my master Calender.

Hi Guys, 

I want to Connect my Tasks Table to My master Calendar table so I can select Billing Month - Which are not the same as regular Month.

My Billing Month Table looks like this. 

BillingMonth, StartDate, EndDate, Description

'June 2018', '2018-06-04', '2018-07-01', 'June 4th to July 1st'

 

I have created a master Calendar using the following Script.

QuartersMap:
mapping Load
RowNo()as MonthStart,
'Q' & ceil (rowno()/3) as Quarter
AutoGenerate (12);

TEMP:

Load
Min(TaskDate) AS MINDATE,
Max(TaskDate) as maxdate
Resident Tasks;

let varmindate = Num(Peek('mindate', 0, 'TEMP'));
let varmaxdate = Num(Peek('maxdate', 0, 'TEMP'));
drop table TEMP;

TempCalender:
Load
$(varmindate)+ IterNo()-1 as Num,
Date($(varmindate)+ IterNo()-1 )as TempDate
AutoGenerate 1 while $(varmindate)+ IterNo()-1 <= $(varmaxdate);


MasterCalender:
load
TempDate as TaskDate,
week(TempDate) as week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate)as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
week (WeekStart(TempDate))&'-'& WeekYear(TempDate)as weekYear,
WeekDay(TempDate) as weekDay
Resident TempCalender
order by TempDate asc;
Drop table TempCalender

Task Table and MasterCalender are Joined.

Any Ideas how i Join my billing Month with my master Calender so I can get Tasks for the Month.

 

Thank you

Labels (2)
1 Reply
dplr-rn
Partner - Master III
Partner - Master III

Sounds like you have multiple date fields . you may need to use canonical dates. see below
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578