Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Dimension used in filtering has nested aggregation

I have dimension name Test Result (Dimension  Values : Success , Suspect , Failed ) 

These are derived from a Update Score ( measure values : 1, 2,3 ) 

Update score is calculated by range comparison in if then else    i.e

update score =  [  if  avg( min a + min b )   between  2 and 5  then  1  else so on ] 

I am getting invalid dimension error for below expression used in dimension. 

=pick( $(V_Update_Score), 'Success', 'Suspect', 'Failed')

However, same expression used as measure works fine. But measure cannot be used for filtering data. 


Please help and advise. 

Labels (2)
1 Reply

You need to wrap each aggregation function (in this case, Avg) in an Aggr() function, because you can't have an aggregate function (Sum, Avg, Count, etc.) in a dimension (it will work in a measure). What field is used to identify an individual Test? If it is TestID, for instance, you would use:

If (aggr(avg(min a + min b),TestID) between 2 and 5 then1 else so on

Use  aggr(avg(min a + min b),TestID)  each time in the if statement, instead of just  avg(min a + min b)

Hope that helps.