qliksenseqlik-expressionset-analysis

Set Analysis to Iterate Over Two Fields that Are Equal to Each Other


I am trying to return a table with FieldA and a measure that returns the count of FieldC where FieldA = FieldB divided by the total count of FieldC (no issue with the denominator).

Below measure works but I don't want to list out every possible option

(COUNT({<FieldA = {'Option1'}, FieldB = {'Option1'}>} Distinct FieldC) 
/ COUNT (TOTAL Distinct FieldC))
+
(COUNT({<FieldA = {'Option2'}, FieldB = {'Option2'}>} Distinct FieldC) 
/ COUNT (TOTAL Distinct FieldC))
+
(COUNT({<FieldA = {'Option3'}, FieldB = {'Option3'}>} Distinct FieldC) 
/ COUNT (TOTAL Distinct FieldC))

Here is what I tried that would be less code but it only works when both FieldA and FieldB are in the UI table itself

if(FieldA = FieldB , COUNT(Distinct FieldC)) / Count (TOTAL Distinct FieldC),0)

Solution

  • Try this in a straight table with FieldA as dimension:

    Count(Distinct If(FieldA = FieldB, FieldC))/Count(Total Distinct FieldC)
    

    I'm pretty sure FieldA and FieldB don't have to be in the UI table together.