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

Rajat1 — Mon, 03 Apr 2023 11:25:04 GMT

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

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

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