Beast Mode Not Allowing Card to Save - AVG SLA BEAST CALCULATION, HELP?

Shumilex

Hi,

I previously created a table card where I was able to add the beast mode below but when I needed to make an edit I was unable to save as it gave the Unable to Save your card contact your Domo Support Team. Below is the table card I am trying to save:

The beast mode for AVG SLA used. When I delete this beast mode I am able to save once again.

SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`closedDate`,`createdDate`))))

Below is how the data looks. I just want to calculate the difference between Created and Closed dates and displaying it as hours to get the AVG SLA by Ticket Type since I am trying to create a KPI Report which reflects totals and not individual records.

Can someone please help with the beast mode... maybe an alternate way to do this?

jaeW_at_Onyx

For 'best practices' if you have a derived column like timediff (closeddate, created date), because the values are on the same row, you should create that column in ETL .

That said, to address your question, simplify. there's no reason for the two nested sec_to_time functions

SEC_TO_TIME(AVG( TIMEDIFF(`closedDate`,`createdDate`)))

Things to confirm when you're building beast modes.

What does each function output? What is the result of TimeDiff? (is it the number of seconds or minutes? I literally do not know, but you should) The reason it's important is b/c you have to know if you can take the avg. of the thing you're outputting.

what's the result of the avg function? (i mean ... that's dependent of TIMEDIFF) and then, can your sec_to_time() function convert that successfully?

when you're trying to save your beast mode, if it doesn't save,

1) make sure it validates

2) try giving it a new name.

GrantSmith

Hi @Shumilex

Have you also tried using a different function to get the time difference in seconds like UNIX_TIMESTAMP?

SEC_TO_TIME(AVG(UNIX_TIMESTAMP(`closedDate`) - UNIX_TIMESTAMP(`createdDate`)))

Shumilex

Hi,

@jaeW_at_Onyx The beast mode saves - but adding the beast mode as a field in the pivot table card is when it doesn't allow the card itself to save. The beast mode validates and I have tried renaming several time with no change. I even tried it without double nesting and it does not allow it to save.

 I created the time diff column and it displays the results I want hh:mm:ss, however when adding the field to the table it is displayed as a count - with no way of deselecting this.

I really just want the date diff/time diff between Created and Closed Dates but displayed as an average time hh:mm:ss format. This is to display the average time it takes to resolve a specific ticket type.

@GrantSmith I did try your formula and it gave the same results and the same issue with being unable to save the card.

Thanks so far but still having the issue...

jaeW_at_Onyx

@Shumilex , IF beast modes only allow you to COUNT it implies that the value being output is TEXT. You can't take the average of text.

Try just

AVG(UNIX_TIMESTAMP(`closedDate`))

and a separate beast mode

AVG(UNIX_TIMESTAMP(`createdDate`))

Given what you've described, i'll bet there are some cases where the row is returning NULL in one of the two columns.