Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
elie_issa
Creator II
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)

          

ali_hijazi

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
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
Creator II
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 ?

sunny_talwar

May be by creating flags in the script?

elie_issa
Creator II
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.

sunny_talwar

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
Creator II
Creator II
Author

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
Creator II
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 ?