Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
RoyBatty
Contributor III
Contributor III

Set analysis - intersection doesn't give the desired result

Hi,

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

RoyBatty_0-1699299161908.png

 

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?

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
mikaelsc
Specialist
Specialist

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

mikaelsc_0-1699373825546.png

 

View solution in original post

6 Replies
mikaelsc
Specialist
Specialist

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
Contributor III
Contributor III
Author

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?

RoyBatty_0-1699311403890.png

 

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 "=":

RoyBatty_1-1699311562248.png

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

RoyBatty_2-1699311660994.png

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

RoyBatty_4-1699312024689.png

 

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

RoyBatty_3-1699311880013.png

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_
Specialist
Specialist

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

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

mikaelsc_0-1699373825546.png

 

RoyBatty
Contributor III
Contributor III
Author

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