Use calculated dimension value in set analysis

gmenoutis — Sat, 16 Nov 2024 05:25:29 GMT

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

Re: Use calculated dimension value in set analysis

gmenoutis — Mon, 01 Jul 2019 12:53:38 GMT

Apparently, one may apply a set analysis filter based on a calculation by using the calculation in a search mask:

[Customer Code]={"=only(HugeCalculatedDimensionFormula)=Value"}

Since the formula was already needing a dollar sign evaluation level, each double quote inside HugeCalculatedDimensionFormula has to be REPLACE()d with two double quotes in order to be escaped.

topic Re: Use calculated dimension value in set analysis in App Development

Use calculated dimension value in set analysis

Re: Use calculated dimension value in set analysis