Hi Friends,
I am having trouble writing an expression to get the following result
Im trying to plot a percentage trend chart with the below expression -
Numerator is counting distinct customer with Set Analysis where a_flag=y and for a quarter data range; however denominator should count all the distinct customer by excluding all the set analysis filter used in the numerator.
The problem Im facing with this is, since the denominator has no date filter; it shows data for all the date range and whereas numerator shows only for the mentioned quarter.
| Table B |
| Client_Id |
Value |
flag |
date |
| 1 |
10 |
1 |
jan |
| 2 |
10 |
1 |
jan |
| 3 |
10 |
1 |
jan |
| 2 |
10 |
0 |
feb |
| 3 |
10 |
1 |
feb |
| 4 |
10 |
1 |
feb |
| 5 |
10 |
1 |
feb |
| 1 |
10 |
0 |
mar |
| 2 |
10 |
0 |
mar |
| 3 |
10 |
0 |
mar |
| 4 |
10 |
1 |
mar |
| 5 |
10 |
1 |
mar |
| 1 |
10 |
1 |
apr |
| 2 |
10 |
1 |
apr |
| 3 |
10 |
1 |
apr |
| 4 |
10 |
1 |
apr |
| 5 |
10 |
1 |
apr |
| 6 |
10 |
1 |
apr |
| 1 |
10 |
1 |
may |
| 2 |
10 |
1 |
may |
| 3 |
10 |
1 |
may |
| 4 |
10 |
1 |
may |
| 5 |
10 |
1 |
may |
| 6 |
10 |
1 |
may |
| Expected Result |
| Month |
Percentage |
|
| jan |
43% |
3/7 |
| feb |
43% |
3/7 |
| mar |
29% |
2/7 |
| Getting |
Count(distinct {<date ={'jan','feb','mar'}, flag={1} >} Client_Id) / Count(distinct Client_Id) |
| Month |
Percentage |
|
| jan |
100% |
3/3 |
| feb |
75% |
3/4 |
| mar |
40% |
2/5 |
| apr |
0% |
0/6 |
| may |
0% |
0/5 |
Thank you for your help, it is greatly appreciated and helpful
