Countif for calculated field

Question & Answer
,
1

Looking for a way to make a scorecard via the example in the red box that sums number of entries in the calculated field column of the yellow box that are over 100%. Countif functions do not work because it is a calculated field. Have not been successful using more advanced functions. Any advice would be appreciated.

image
image1301×775 83.1 KB

2

Hi @brendaca and welcome to the QuickSight community!
Could you share what the calculated field is for percent of planned?

Additionally, have you tried building an ifelse statement that includes the function(s) being used for your ‘percent of planned’ field?

You mentioned that it’s not allowing you to use the ‘countIf’ function since it’s a calculated field, are you receiving an error? And if so, could you share what you tried to create and what the error you’re receiving is?

3

Brett,

Thank you for reaching out. The calculated field is the task_actual_effort against task_planned_effort.

I have not been successful researching how to syntax the string for an ifelse statement.

Not getting any feedback on the error when trying to create the field. For reference this is a visual concept of what I am trying to achieve. I have the table and filters and am trying to build score cards that break down how many rows in the table fall into certain percentage brackets.

image
image1566×1320 119 KB

4

Hi @brendaca,
Apologies for missing your last response. So to make sure I correctly understand;

  • The ‘percent of planned’ field is a calculated field based on on ‘task_actual_effort’ and ‘task_planned_effort’?
  • Ideally, you’d like to run a count for the rows’ percent of planned field and categorize them in the percentage buckets that are colored in your second visual?

What is the syntax for the calculated field?
In this scenario, we may need to take that calculated field and nest within a distinctCountOver function to create those counts so it would be helpful to see that calc. field a bit more in depth.

1 Like
5

Hi @brendaca

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

Hi @brendaca,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant context if needed.

Thank you