Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Date Not working properly in Set analysis

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

View solution in original post

11 Replies
Highlighted

Re: Date Not working properly in Set analysis

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

Highlighted
Creator II
Creator II

Re: Date Not working properly in Set analysis

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 ?

Highlighted

Re: Date Not working properly in Set analysis

May be by creating flags in the script?

Highlighted
Creator II
Creator II

Re: Date Not working properly in Set analysis

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.

Highlighted

Re: Date Not working properly in Set analysis

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

View solution in original post

Highlighted

Re: Date Not working properly in Set analysis

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

Highlighted
Creator II
Creator II

Re: Date Not working properly in Set analysis

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

Highlighted

Re: Date Not working properly in Set analysis

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

Highlighted
Creator II
Creator II

Re: Date Not working properly in Set analysis

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 ?