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

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
gmenoutis
Partner - Creator II
Partner - Creator II

Use calculated dimension value in set analysis

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

 

 

Labels (2)
1 Solution

Accepted Solutions
gmenoutis
Partner - Creator II
Partner - Creator II
Author

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.

 

View solution in original post

1 Reply
gmenoutis
Partner - Creator II
Partner - Creator II
Author

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.