Question

# Count of greater than and less than values

• 457 views

• New Member
• 0 replies

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 dimensiondimension: 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.

Thanks

This topic has been closed for comments

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!

Thanks,

Eric