Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Master Calendar Issue

Dear All,

Please i need your assistance in the below schema (check attachment)  where we are facing difficulties when trying to generate the vacancies by dates.

For example if we choose a vacancy id in our table it will disappear (check capture 1 attachment)  because the vacancy Start date is in 27/7/2017 while the master calendar date is showing dates based on the assignment dates between 1/9/2017 and 31/10/2017

We need to generate the data based on vacancy dates and the total count expression that we are using is the below :


count ({<

[Hired Flag] = {'Yes'},

[Vacancy SDate] = {"$(= '>=' & date($(vBOMFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

>}

distinct [Person Id])

and the vBOMFilterDate is :

RangeMax(

Min(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)

    >}Num([Master Calendar Date]))

,YearStart(vEOMFilterDate))

and vEOMFilterDate is :


Max(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

NOTE : When choosing a vacancy code as shown in Capture 1 attachment the total count expression is getting null records but if we don't choose any vacancy the data is shown.

Thanks for your support.

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

I found the solution using below expression :

if (GetSelectedCount([Vacancy Code])=0 ,

count ({<

[Hired Flag] = {'Yes'},

[Person Type Category] = {'Applicants'},

[Vacancy SDate] = {"$(= '>=' & date($(vBOMFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

>}

distinct [Person Id]),

count ({<

[Hired Flag] = {'Yes'},

[Person Type Category] = {'Applicants'},

[Vacancy SDate] = {"$(= '>=' & date($(vVacancyFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

>}

distinct [Person Id]))

View solution in original post

2 Replies
Highlighted
Master II
Master II

Are you missing dates for Vacancy ID?

I don't understand, cus Master Calendar dates can be generated as we want but it would be useless if your fact is missing those dates.

Is your fact Table missing dates for Vacancy ID?

Highlighted
Creator II
Creator II

I found the solution using below expression :

if (GetSelectedCount([Vacancy Code])=0 ,

count ({<

[Hired Flag] = {'Yes'},

[Person Type Category] = {'Applicants'},

[Vacancy SDate] = {"$(= '>=' & date($(vBOMFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

>}

distinct [Person Id]),

count ({<

[Hired Flag] = {'Yes'},

[Person Type Category] = {'Applicants'},

[Vacancy SDate] = {"$(= '>=' & date($(vVacancyFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

>}

distinct [Person Id]))

View solution in original post