Date Not working properly in Set analysis

[elie_issa]

Dear All,

I am trying to generate results using below expression. The results are totally fine when selecting a week year but they are totally wrong when not filtering because the selection of the date below is not taking the master calendar of the same row.

Expression :

count({<

          [Country]          = {'test'},

        [Nationality]      = {'test'},                   

      [Group Engagement Date]  = {"<=$(=date($(vMasterCalendar)))"},

      [Group Termination Date] = {">=$(=date($(vMasterCalendar)))"},

        [Assignment SDate]      = {"<=$(=date($(vMasterCalendar)))"},

        [Assignment EDate]      = {">=$(=date($(vMasterCalendar)))"},

        [Person SDate]          = {"<=$(=date($(vMasterCalendar)))"},

        [Person EDate]          = {">=$(=date($(vMasterCalendar)))"},

      [Person Type Category]  -= {'Daily Hired'}

    />}distinct [Employee Number])

vMasterCalendar :    =only({<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Week Year]=P([Week Year]),[Month                                                        Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

Results : Capture attached right number highlighted in yellow and the results are fine when choosing a week year but they are wrong when we don't make any filter (Wrong Numbers Attach)

ali_hijazi‌

[sunny_talwar]

Set analysis is evaluated once per chart and what you are looking to do is to evaluate your expression on a row by row basis... in order to do this... use if statement (with may be Aggr) to do this on a row level

[elie_issa]

I have other expressions also having same logic. i need to get the whole data by week year then get the average of the last 26 weeks so i need to do it row by row first and i need to get correct data. I am trying not to use if condition because you know it will affect the dashboard performance. any other solution ?

[sunny_talwar]

May be by creating flags in the script?

[elie_issa]

yes we tried to create flags and we have been so close in numbers but not exactly the same numbers and this is affecting our average that's why i am trying to find an alternative solution using the master calendar found in each row by week year if possible to do it.

[sunny_talwar · Accepted solution]

Then I don't think you have an option other than using if statement

[sunny_talwar]

One options might be this... but not sure how better it is compared to if

Count({<[Country] = {'test'}, [Nationality] = {'test'}, [Employee Number] = {"=[Group Engagement Date]  <= $(vMasterCalendar) and [Group Termination Date] >= $(vMasterCalendar) and [Assignment SDate] <= $(vMasterCalendar) and [Assignment EDate] >= $(vMasterCalendar) and [Person SDate] <= $(vMasterCalendar) and [Person EDate] >= $(vMasterCalendar)"}, [Person Type Category]  -= {'Daily Hired'}>} DISTINCT [Employee Number])

[elie_issa]

and how should i do it if  i am want to use if and Aggr ?

[sunny_talwar]

Did the expression above not work? Just want to know before I suggest an Aggr() and if

[elie_issa]

it's giving zeros.

I think the main problem is from the below variable :

vMasterCalendar :    =only({<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Week Year]=P([Week Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

What should we put in this variable to get the master calendar of the same row of the week year ?