Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table showing staff details
Column headings are
Staff No
StartDate
LeaveDate
I want to create a chart that shows the count of starters & leavers each YearMonth
I can create a dimension StartDate.YearMonth or LeaveDate.YearMonth. How can I create a common dimension YearMonth? so I can show the count of starters & leavers each month?
Main:
Load
Staff,LeaveDate,StartDate
From Xyz;
DateBridge:
Load StartDate as Date
,'StartDate' as Datetype
Resident Main;
Concatenate(DateBridge)
Load LeaveDate as Date
,'LeaveDate' as Datetype
Resident Main;
//MasterCalendar:
//ADD Mastercalendar script here that links to Date field in DateBridge table
in Charts Use the Date field
example
Dimension : Months(from master calendar)
Measure1: # of Starters = count({<DateType={'StartDate'}>}Staff)
Measure2: #of leavers = count({<DateType={'LeaveDate'}>}Staff)
read below
https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/