Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
elie_issa
Contributor 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
elie_issa
Contributor II

Re: Master Calendar Issue

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]))

2 Replies
Khan_Mohammed
Honored Contributor II

Re: Master Calendar Issue

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?

elie_issa
Contributor II

Re: Master Calendar Issue

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]))