Convert numbers based on some criteria and then sum them

Question & Answer
1

Hello team, I want to know if this is possible:

I have created a table that contains the time spent on some tickets, so I need to make a sum of this time, but I need to change some data based on the following criteria:

  1. If the ticket is duplicated, I need to do a time average and then use this number for the total sum.
  2. If the ticket is not duplicated but has more than 12 hours, then convert that number to 5 hours.
  3. If the ticket is not duplicated and does not have more than 12 hours using the hours reported.

This image is an example of what some data looks like:
image

The problem is that I need to see the hours per week, so when I remove the issue_key column, the sum is not done correctly
image

The table should look like this
image

This is the custom field I did, but It only works when I have the issue_key column and I’m not sure if this problem can be solved from a calculated field.

ifelse(
count({issue_key})>1,avg({hours_in_status})*distinct_count({issue_key}),
count({issue_key})=1 AND sum({hours_in_status})>10, 5,
sum({hours_in_status}))
2

The issue is arising because you want to see if the issue_key is a duplicate for the particular issue_key.

When you take away the break down of issue_key from the table count({issue_key}) is looking at all of the issue_key’s not counting the duplicates of each one.

This can’t be done via a calculated field without you having to reference the issue_key in the table.

I would suggest moving this logic to SQL. For this logic you would need to see if the issue key is a duplicate and flag that. And take you’ll need the avg of that issue_key if it’s a duplicate.

This can be done with a countOver / avgOver and partition by the issue_key in SQL.

From there, in quicksight you can say

ifelse({duplicate_flag}=‘True’,{avg_over_hours_in_status},{duplicate_flag}=‘False’ & {hours_in_status}<12,5,{hours_in_status})

Then take the sum of this ^

Let me know if that helps

3

Thanks @Max. I found a simpler solution with a calculated field. I’ll leave it here in case anyone has a similar situation.

image
image632×772 50.3 KB

ifelse(
    min({hours_in_status},[{issue_key}])>12,5,
    min({hours_in_status},[{issue_key}])
)

First I take the minimum hours from the issue_key, with this I can fix the problem of duplicates (previously the solution was the average of the hours), and then I can apply the correction for the high hours. Finally, I just need to apply the arithmetic operation I want.

1 Like
4

Thanks for sharing your solution with the community @DannyV! Have a great weekend!