Calculated Field for Pending Escalations

I have two dates for reference, Escalation Received Dates and Escalation Closed Dates. I want to create a visual to graph which would calculate pending escalations by end of every week (23.59 hrs Saturday) for the last three years on my Dashboard.

1 Like

Hello @arjunkal, I have some ideas on how you can implement this, I just want to clarify something. Are you wanting to show the total number of hours since the escalation was received if it wasn’t closed for that week? My thought is we will create a calculated field that will check if the Received Date and Closed Date are not within the same week, then we can calculate the pending time for that week. Then, if you put the field in a visual that is aggregating the Received date by Weeks, it should function who you are expecting. The only thing I am unsure of, is if this will properly aggregate pending escalations that are not closed over multiple weeks, but we can give it a try.

Pending =

 ifelse(
truncDate('WK', {Escalation Received Dates}) <> truncDate('WK', {Escalation Closed Dates}),
(((7-extract('WD', {Escalation Received Dates}))*24) - (24 - extract('HH', {Escalation Received Dates}))),
NULL
)

We may need to figure out a way to better check for closed dates, but the calculation I wrote should provide you with the hours between the Received Date and EoD Saturday. I hope this helps!

@DylanM - Thanks. To be very precise, we dont need hours as such. Infact if we can calculate the pending escalations, by the start of every week, that would do our job.

1 Like

@DylanM Could you please provide me a calculation for pending escalations in numbers at the start of the every week (Received but not closed).

Hello @arjunkal, if you just want the count of pending escalations per week, then you would want to change the output of the ifelse statement and wrap it in something like a distinctCountOver function.

You also would want to create your truncDate Week values for both date fields as calculated fields to avoid errors in the syntax.

Escalation Received Dates Week = truncDate('WK', {Escalation Received Dates})
Escalation Closed Dates Week = truncDate('WK', {Escalation Closed Dates})

Then use them in this field, in combination with some kind of ID field related to your escalations:

distinctCountOver(ifelse(
{Escalation Received Dates Week} <> {Escalation Closed Dates Week},
{Escalation ID},
NULL
), [{Escalation Received Dates Week}], PRE_AGG)

That should help you get the distinct count of escalations per week. I will mark this as the solution, but let me know if you have any remaining quesitons.

1 Like

Thanks @DylanM - The above calculated field did work for the present week, but it didn’t work for previous weeks Open escalations during that period.

Hello @arjunkal, the problem that you are going to face is you do not have a date outside of the received and closed dates so there is not way to compare values outside of the received date. In order to properly manage this, you would need the Received Date, Closed Date, and something like an Update Date so you can compare the received date against something other than itself.

It would look like this:

ifelse(
{updated date week} = {Escalation Received Dates Week} AND {Escalation Received Dates Week} < {Escalation Closed Dates Week}, {Escalation ID},
{updated date week} > {Escalation Received Dates Week} AND {updated date week} < {Escalation Closed Dates Week}, {Escalation ID},
{updated date week} > {Escalation Received Dates Week} AND {updated date week} >= {Escalation Closed Dates Week}, NULL,
NULL
)

If you can bring in a scaling date field outside of your received and closed dates, you can definitely make this function dynamically. But without it, QuickSight cannot create a date or determine each week between received and closed dates, so it won’t work outside of the week it was received.

1 Like