topic Re: Possible in Set Analysis to count distinct dates based on two separate fields? in QlikView

Possible in Set Analysis to count distinct dates based on two separate fields?

Tyler_Waterfall — Tue, 07 Jul 2015 20:16:54 GMT

I am trying to count the unique [Date] values which either had [flag_A]=1 OR [flag_B]=1. Is this possible?

[flag_A] and [flag_B] are independent fields on 'associated' tables, but which will never both equal 1 in the same 'record' of the tables. (Think of it as linking table A which is activity "starts" and table B which is activity "finishes".)

Seems like an if statement works:

count(distinct if(flag_A=1 or flag_B=1,Date))

but I wondered if such is possible and sensible with set analysis.

Re: Possible in Set Analysis to count distinct dates based on two separate fields?

sunny_talwar — Tue, 07 Jul 2015 20:21:41 GMT

Not sure if this will work, but try this:

=Count(Distinct {<flag_A = {1}>+<flag_B = {1}>} Date)

Re: Possible in Set Analysis to count distinct dates based on two separate fields?

Qrishna — Tue, 07 Jul 2015 20:25:40 GMT

I am afraid if i had understood your situation.

but i would try writing the below expressions Also.

if(flag_A=1 or flag_B=1, count(distinct{$}Date))

if( (flag_A=1 and flag_B=0) or (flag_A=0 and flag_B=1) , count(distinct{$}Date))

Count(Distinct {$<flag_A=,flag_B= >} Date)

let me know if any of the expressions works for your situation.

We can proceed resolving your issue from there onwards.

krishna

Re: Possible in Set Analysis to count distinct dates based on two separate fields?

jonasheisterkam — Tue, 07 Jul 2015 20:29:04 GMT

if i understood it right you search the XOR(/) or OR(+)

count({<flag_A={1}>/<flag_B={1}>} distinct Date)

count({<flag_A={1}>+<flag_B={1}>} distinct Date)

Re: Possible in Set Analysis to count distinct dates based on two separate fields?

Tyler_Waterfall — Wed, 08 Jul 2015 13:18:30 GMT

All these years and I never caught that I could define separate sets with the < >.

Thank you for your quick posts!

Here's what I ended up with:

(sum(TaskExecution)+sum([Reload in Hub]))/count({<TaskExecution={1}>+<[Reload in Hub]={1}>} distinct Date)

which as far as I can tell is equivalent to:

(sum(TaskExecution)+sum([Reload in Hub]))/count(distinct if(TaskExecution=1 or [Reload in Hub]=1,Date))

Re: Possible in Set Analysis to count distinct dates based on two separate fields?

sunny_talwar — Wed, 08 Jul 2015 13:21:04 GMT

which as far as I can tell is equivalent to:

(sum(TaskExecution)+sum([Reload in Hub]))/count(distinct if(TaskExecution=1 or [Reload in Hub]=1,Date))

You are right Tyler