topic Re: Date Not working properly in Set analysis in Visualization and Usability

Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 16:03:21 GMT

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‌

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 16:09:33 GMT

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

Re: Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 16:13:33 GMT

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 ?

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 16:15:39 GMT

May be by creating flags in the script?

Re: Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 16:24:54 GMT

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.

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 16:26:07 GMT

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

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 16:29:50 GMT

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

Re: Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 16:42:59 GMT

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

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 16:49:05 GMT

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

Re: Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 16:54:50 GMT

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 ?

Re: Date Not working properly in Set analysis

elie_issa — Wed, 25 Jul 2018 17:29:26 GMT

it's working fine now thanks for the help sunny

count({<

[Country] = {'test'},

[Nationality] = {'test'},

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

}distinct (if( [Group Engagement Date] <= [Master Calendar Date]

and [Group Termination Date] >= [Master Calendar Date]

and [Assignment SDate] <= [Master Calendar Date]

and [Assignment EDate] >= [Master Calendar Date]

and [Person SDate] <= [Master Calendar Date]

and [Person EDate] >= [Master Calendar Date] ,[Employee Number])))

Re: Date Not working properly in Set analysis

sunny_talwar — Wed, 25 Jul 2018 17:38:17 GMT

This should work also,

Count({<[Country] = {'test'}, [Nationality] = {'test'}, [Employee Number] = {"=

[Group Engagement Date] <= [Master Calendar Date]

and [Group Termination Date] >= [Master Calendar Date]

and [Assignment SDate] <= [Master Calendar Date]

and [Assignment EDate] >= [Master Calendar Date]

and [Person SDate] <= [Master Calendar Date]

and [Person EDate] >= [Master Calendar Date]

"}, [Person Type Category] -= {'Daily Hired'}>} DISTINCT [Employee Number])