Master Calendar Issue

[elie_issa]

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.

[MK9885]

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 · Accepted solution]

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