Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elie_issa
Contributor 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

Re: Date Not working properly in Set analysis

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

11 Replies

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

elie_issa
Contributor 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 ?

Re: Date Not working properly in Set analysis

May be by creating flags in the script?

elie_issa
Contributor 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.

Re: Date Not working properly in Set analysis

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

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

elie_issa
Contributor II

Re: Date Not working properly in Set analysis

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

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

elie_issa
Contributor 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 ?

Community Browser