I need to calculate the following metric: From the clients with [A/L/D/P]={Active}, find the percentage where [Overall Consent]*={Given}. This resulted in the following correct formula:
num
(
count({<[A/L/D/P]*={Active},[Overall Consent]*={Given}>}distinct [Customer Code])
/count({<[A/L/D/P]*={Active}>}distinct [Customer Code])
)
However, I am now developing a new formula, where A/L/D/P is dynamically calculated based on a user's field inputs (changing rolling time). I have created a calculated dimension using aggr on [Customer Code], and it is rather complex. How can I now use a set analysis on the value of the calculated dimension, which is as I undeerstand not a part of the data model?
The new [A/L/D/P R] calculated dimension has the following formula:
$(='pick(
aggr(
rangemin(
8*if(only([Registration Date])<=max(total [Rolling Date]),0,1)
+3*(1+sign(count({<[Transaction Date]={">' & addmonths(max(total [Rolling Date]),-12) & '<=' & max(total [Rolling Date]) & '"}>} distinct [Transaction ID])-1))
+pick(
1
+2*if(count({<[Transaction Date]={">' & addmonths(max(total [Rolling Date]),-24) & '<=' & addmonths(max(total [Rolling Date]),-12) & '"}>} distinct [Transaction ID])>=1,1,0)
+ if(count({<[Transaction Date]={"<=' & addmonths(max(total [Rolling Date]),-24) & '"}>} distinct [Transaction ID])>=1,1,0)
,0,1,2,2)
+1+sum(0)
,8
)
,[Customer Code]
)
,''Prospect'',''Dormant'',''Lapsed'',''Active'',''Active'',''Active'',''Active'',null())')
