Count of greater than and less than values

  • 30 August 2021
  • 1 reply

Hi Everyone,


I have a dimension that calculates user engagement score and based on that I want to calculate the total number number of users based on each engagement type.

dimension: enagagement_score {
    label: "enagagement_score"
    group_label: "Engagement"
    type: number
    sql: (${scroll}/100) * 0.3 + (${time}/600) * 0.7 ;;

#then another dimension

dimension: engagement_type {
label: "engagement_type"
type: string
group_label: "Engagement"
sql: CASE WHEN ${enagagement_score} <= .1 THEN 'Low' WHEN ((${enagagement_score} > .1) AND (${enagagement_score} <=.5)) THEN 'Medium' WHEN ${enagagement_score} > .5 THEN 'High' ELSE NULL END ;;

# Now I want to calculate the total number of Low, Medium and High engagement numbers

measure: engaged_user_low {
type: count_distinct
label: "engaged_user_low"
group_label: "Count"
sql: (${enagagement_score} <= .1);;

#but this is returning only 2 - presuming its just true and false.

How do I calculate total number of users based on each engagement type, i.e. Low, Medium and High.



1 reply

Userlevel 4

Hi @rahul-snowplow,


You potentially could use a filtered measure. This article walks through the process of setting that up, you could create three filtered measures, one for count of low, one for count of medium, one for count of high. 


Alternatively, you could make a tier dimension and then select that and a count measure in the explore, but the filtered measure would most likely be easier. 


I would personally test this by select the engagement_type dimension and selecting a count measure. Then, to see more granular results, I would select the enagagement_score dimension.  I would try to look for line items that are unexpected or counts that show zero when the we think it should show > 0. Another tip would be to check the generated sql and make sure that looks correct.


Please let us know if you have any questions!