Calculated field to show busiest day

Question & Answer
,
1

Hi struggling to find a calculation to find the date on which a measure has it’s highest value. Exact use-case is showing the date on which a telephone agent took the highest number of calls. I need to display this in a table hence the need for a calculated field.

2

Hi @Mikewest,

How does your dataset look like? What does each row in the dataset represent?

1 Like
3

Hi @Mikewest
did you checked out

BR

1 Like
4

Hi @David_Wong,

Each row represents a single phone call: with a Call ID, timestamp (YYYY-MM-DD hh:mm:ss), Agent Name and duration measures.

On a dashboard that will always be filtered to a single Agent, I would like to show stats for the agent’s entire tenure. I am hoping to aggregate a distinct count of the Call ID by day, then find the date (in YYYY-MM-DD format) with the max number of calls and the number of calls taken on that day. I’d like to show these in a table also displaying total contacts during the agent’s tenure, average contact duration and total time on a contact.

image

@ErikG thank you for the pointer here, I had not discovered this function yet. I tried the following Busiest Day calculation but I get a SQL error when I try to add it to visual/it won’t let me add this as a grouping to a table.

Contact Start Day:

truncDate('DD', {Contact Start Time})

Calls Per Day:

distinctCountOver({Contact ID},[{Contact Start Day}],PRE_AGG)

Busiest Day:

lastValue(
    {Contact Start Day},
    [{Calls Per Day} DESC],
    [{Contact Start Day}]
)

Appreciate both of your assistance on this.

5

Found a workaround that doesn’t require a formula. Handled it with a separate table, with a Top N filter on Contact Start Time (DAY) driven by distinct count of Call ID. Then I made the table backgrounds transparent and overlapped them so they just look like segment breaks in the same table:

image

Would still love to know if there is a custom calculation solution to this, just for my own learning.

1 Like
6

So it sounds like you were able to find the busiest day by using a Top N filter.

Is the issue that when you apply the filter, it’s also getting applied to your other calculated fields? The way around that is to use LAC-W with PRE_FILTER for your other calculated fields so that they don’t get affected by the filter.

For example:
Start Date = minOver({Contact Start Time}, [{Agent Name}], PRE_FILTER)
End Date = maxOver({Contact Start Time}, [{Agent Name}], PRE_FILTER)

Each of your calculated fields will be displayed as a column in your table visual. To display the columns as rows, click on this icon:
image

1 Like