Set analysis - intersection doesn't give the desired result

[RoyBatty]

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?

[mikaelsc]

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) 

[RoyBatty]

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:

[marcus_sommer]

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

[vincent_ardiet_]

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

[mikaelsc · Accepted solution]

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

[RoyBatty]

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