topic Re: Set analysis - intersection doesn't give the desired result in QlikView

Set analysis - intersection doesn't give the desired result

RoyBatty — Mon, 06 Nov 2023 20:01:21 GMT

Hi,

There is the following information (please only look at what is in the red rectangles, the rest is irrelevant):

There is a dimension "MonthYear" and two measures: "E" and "N".

E is the number of distinct users who had Payment or Cost during MonthYear, and for that I use the following expression:

Count({<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Cost'}>} distinct [%_user_id])

and it works fine. So, in the example from the picture, for "Dec 2022" only 2 users ("John Doe" and "Mike Smith") had balance change (Payment and/or Cost).

However, for N I am not sure how to write the expression.

N is the number of users who Registered during the MonthYear AND who also had a Payment or Cost during that same MonthYear. So, in the example from the picture, for "Dec 2022" it should be 1 (only "Mike Smith").

I tried using the following expression:

Count({<[Standard Activity Period]={'Y'}, [Activity]={'Registration'}>*<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Cost'}>} distinct [%_user_id])

but it doesn't work (it returns 0 instead of 1 for the case above). My "logic" with that expression is:
Count (INTERSECTION of users who Registered AND users who had Payment or Cost)
- but apparently it doesn't work.

Do you maybe know how I could solve this, how the expression should look?

Re: Set analysis - intersection doesn't give the desired result

mikaelsc — Mon, 06 Nov 2023 20:24:28 GMT

i think your intersection is not really an intersection. you are basically saying give me the intersection of the factIDs that are registrations and activity's at the same time.

try something like: (have fun with enquoting what needs to be enquoted)

count(distinct {<

user_id =

{"=Count({<[Standard Activity Period]={'Y'}, [Activity]={'Registration'}>}distinct [%_user_id])>0"}

{"=count(<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Cost'}>}distinct [%_user_id])>0"}

user_id)

i guess replacing the green user_id by %factID would make it easier to read/understand

(counting the number of user_ids that have a >0 Registration and a >0 number of activity's)

Re: Set analysis - intersection doesn't give the desired result

RoyBatty — Mon, 06 Nov 2023 23:08:08 GMT

Hi @mikaelsc ,

I tried that expression but editor was reporting "Error in expression", and I guess that's why you wrote to enquote what needs to be enquoted.

I'm not sure if I understand that expression, I guess it counts that new user_id instead of %_user_id?

so by saying user_id= we are basically setting a new "variable" user_id that will contain %_user_ids that meet the stated criteria that are specified after "=":

Anyway, since Qlik editor was reporting "Error in expression", I enqouted user_id:

but it still doesn't work properly. It seems that it only counts the number of registered users OR it simply always returns the number 1 (not sure which of the two).

For example, for Nov 2022, N should be 0 but it shows 1:

Re: Set analysis - intersection doesn't give the desired result

marcus_sommer — Tue, 07 Nov 2023 12:19:51 GMT

I think it's not solvable with a set analysis - regardless how an intersection is attempted because the queried states belong to different records and therefore the AND connection between them could be never return a TRUE.

This means a multi-step approach will be needed - maybe similar to the suggestion from @mikaelsc but simpler and more efficient would be it not to apply them as if-loop within the set analysis else as boolean condition against the origin expression - maybe something in this way (simplified by skipping not relevant conditions):

Count({<[Activity]={'Payment', 'Cost'}>} distinct [%_user_id]) *
Count({<[Activity]={'Registration'}>} distinct [Activity])

Beside of this you will probably need some more steps because the above will only work within the appropriate dimensionality which seems not be wanted because you showed the results on a MonthYear level. This means the required dimensionality must be added to the expression, for example like:

sum(aggr(
Count({<[Activity]={'Payment', 'Cost'}>} distinct [%_user_id]) *
Count({<[Activity]={'Registration'}>} distinct [Activity]),
[%_user_id], MonthYear))

Re: Set analysis - intersection doesn't give the desired result

vincent_ardiet_ — Tue, 07 Nov 2023 13:28:54 GMT

Maybe something like this (your intersection cannot work as registration and payment/cash are not on the same rows):
Count({<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Cost'},[%_user_id]=p({<[Standard Activity Period]={'Y'}, [Activity]={'Registration'}>} [%_user_id])>} distinct [%_user_id])

Re: Set analysis - intersection doesn't give the desired result

mikaelsc — Tue, 07 Nov 2023 16:17:17 GMT

argh. sorry.. true;

something the hated if's should help

sum(aggr(if(Count({<[Activity]={'Registration'}>}distinct [%_user_id])>0 and count({<[Activity]={'Payment', 'Cost'}>}distinct [%_user_id])>0,1,0),[%_user_id],[Fact Date]))

Re: Set analysis - intersection doesn't give the desired result

RoyBatty — Tue, 07 Nov 2023 21:21:38 GMT

Thank you @mikaelsc !

So, in my case instead of [Fact Date] at the end it would be MonthYear.

sum(
   aggr(
   if(
   Count({<[Standard Activity Period]={'Y'}, [Activity]={'Registration'}>} distinct [%_user_id]) > 0 and count({<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Cost'}>} distinct [%_user_id])> 0,
   1, 0
   ),
   [%_user_id], MonthYear
   )
)