I’m looking into building graphs around SLA metrics. For this example, I have a row of hours and minutes that I want to maintain as time. When an SLA is breached then it displays this row as a minus date (image below). I’m not able to make any calculation due to the minus sign as QuickSight does not format this string to a numeric value. Is it possible to convert the minus time value to an integer where I could calculate what is our weekly average SLA?
When I use the date option then I’m presented with invalid rows which means that the calculation field later cannot use these values as they are marked as strings.
Welcome to the QuickSight community! It is great having you here.
You can try to convert the string with the SLA data to a number, then perform your average calculations and then you can use a similar reversed calculation to present it as an actual time. Might be an easier way to get the number, but this one seems to work.
It does help a lot and I believe I almost have it but there seems to be an issue with the dividing value, for some reason /60 is not recognized as a numeric value. I’m wondering if it could be an issue still with the sla_first sometimes having “:00” values in the end.
I did some syntax adjustments, tried to test out the /60 with different syntax, and also renamed my field to sla_first as it was named with spaces and capital letters before but this did not change anything. This is now what I tried: ifelse(parseInt(split({sla_first},":",1))<0, parseInt(split({sla_first},":",1))-parseDecimal(split({sla_first},":",2)/60), parseInt(split({sla_first},":",1))+parseDecimal(split({sla_first},":",2))/60)
but I got the error:
Expression /60), parseInt(split({sla_first},":",1)) for function / has incorrect argument type String / Numbrr. Function syntax expects '<NUMERIC> / <NUMERIC>'
'<DATETIME_INTERVAL> / <NUMERIC>'.
Could it be the same thing that the sla_first is not recognized still as a numeric field?
Just for more context, here are some examples from the SLA data row that we have in the dataset:
-6:02
8:00
0:05
0:13
7:50
-31:34
Also, some rows are empty as it’s measured as a ticket but the SLA value is not populated as it’s not monitored. Could this be causing issues?
