Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
fintan
Contributor
Contributor

How can I create a common dimension to show dates from more than one field

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? 

 

Labels (3)
1 Reply
vinieme12
Champion III
Champion III

 

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/

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.