Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Rajat1
Partner - Contributor III
Partner - Contributor III

I am stuck in a logic for which involves intersection in set analysis.

Test:
Load*Inline [

Month,user_id
jan,1
jan,2
jan,3
jan,4
jan,5
jan,6
jan,7
jan,8
jan,9
feb,1
feb,5
feb,10
feb,11
feb,12
March,1
March,2
March,3
March,13
March,15];

exit script;

 

This is my data set.

Now my requirement is that i want to have a ratio which should be equal to no of user_id repeated in march from jan but they should not be included in feb.

for example there are 9 user_id in jan and out of which 2 are repeating in feb i.e, 1 and 5 so we will remove that from the base of jan and it comes out to be 7 user_id now we will move to march and we can see that march and jan has 3 common user_id which are 1,2 and 3 but 1 has already been removed while calculating feb user_id so now 2 and 3 remains which are common between jan and feb so now our ration should be common/base which is 2/9.

In short i want a metric named as M-2 which means my denominator will jan and numerator will be common user_id between jan and march but it should not include the user_id which already has been removed in feb month as mentioned in above example.

I tried this in M-1 scenario and it works in that 

formula i used is 

=sum(aggr(count({<Month={'jan'}>}distinct user_id)
*
count({<Month={'feb'}>}distinct user_id),user_id))

Please help in the second metric which M-2 mentioned in starting.

Thanks,

Rajat

Labels (1)
0 Replies