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.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shrutipg
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
GaryGiles
Specialist
Specialist

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.