Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator II

## Date Not working properly in Set analysis

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)

1 Solution

Accepted Solutions
MVP

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

11 Replies
MVP

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

Creator II
Author

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 ?

MVP

May be by creating flags in the script?

Creator II
Author

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.

MVP

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

MVP

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

Creator II
Author

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

MVP

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

Creator II
Author

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 ?

Tags
Community Browser