topic Re: How to Write if condtion expression in set analysis in QlikView

How to Write if condtion expression in set analysis

shabarish0587 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

Can any one help me how to write if condition statement into set expression.

count(DISTINCT if((IsNull((END_DATE) and OPEN_DATE<='2018-10-20') or (END_DATE> '2018-10-20 'and OPEN_DATE<='2018-10-20') ), IDs)).

in the place of 2018-10-20, I defined a variable vWeekDate.

the above expression I need to write same as by using set expression. Can any one help me on this. Thanks in advance

Re: How to Write if condtion expression in set analysis

dplr-rn — Tue, 02 Apr 2019 13:38:18 GMT

try something like below
{< ID={"=Len(END_DATE)=0"}, OPEN_DATE={"<=$(=vWeekDate)"}> // for first and condition
+ < END_DATE={">$(=vWeekDate)"}, OPEN_DATE={"<=$(=vWeekDate)"}> // + for the or
}

Re: How to Write if condtion expression in set analysis

sunny_talwar — Tue, 02 Apr 2019 14:22:19 GMT

Or this

Count(DISTINCT {<IDs = {"=(IsNull(END_DATE) and OPEN_DATE <= MakeDate(2018, 10, 20)) or (END_DATE > MakeDate(2018, 10, 20) and OPEN_DATE <= MakeDate(2018, 10, 20))"}>} IDs)

Re: How to Write if condtion expression in set analysis

shabarish0587 — Wed, 03 Apr 2019 06:47:54 GMT

Hi Sunny,

Thank you for you reply. i have tried by using this expression like this

Count(DISTINCT {<IDs = {"=(IsNull(END_DATE) and OPEN_DATE <= MakeDate(2018, 10, 20)) or (END_DATE > MakeDate(2018, 10, 20) and OPEN_DATE <= MakeDate(2018, 10, 20))"}>} IDs)

i am not getting the values. but i tried belowr method like this i am getting value.

Count(DISTINCT {<IDs = {"=(IsNull(END_DATE) and OPEN_DATE <= '2018-10-20') or (END_DATE > '2018-10-20' and OPEN_DATE <= '2018-10-20')"}>} IDs)

I need to place a field instead of hard coded value.

Re: How to Write if condtion expression in set analysis

sunny_talwar — Wed, 03 Apr 2019 11:02:31 GMT

@shabarish0587 wrote:
i am not getting the values. but i tried belowr method like this i am getting value.
Count(DISTINCT {<IDs = {"=(IsNull(END_DATE) and OPEN_DATE <= '2018-10-20') or (END_DATE > '2018-10-20' and OPEN_DATE <= '2018-10-20')"}>} IDs)

Nice

@shabarish0587 wrote:
I need to place a field instead of hard coded value.

Okay, so what is the issue?

Re: How to Write if condtion expression in set analysis

shabarish0587 — Wed, 03 Apr 2019 14:36:56 GMT

Hi Sunny,

When we are using makedate function. Value is showing Zero. Same expression i am giving hard coding value . I am getting exact out put.

Count(DISTINCT {<IDs = {"=(IsNull(END_DATE) and OPEN_DATE <= '2018-10-20') or (END_DATE > '2018-10-20' and OPEN_DATE <= '2018-10-20')"}>} IDs)

But when we are giving makedate. it is showing Zero. So i need to place a Date Field in the place of Hard code.