I have a line graph. This line graph gives me data for each 30 minute intervals throughout the day. The interval data is for a call center. My main metrics are volume, average handle time, and telephone service factor (a % of calls meeting the service level agreement).
For each interval I have the interval data and the cumulative data up to that point.
I want to use correlations, so I'm using this calculation to spit into the R server.
SCRIPT_REAL("cor(.arg1, .arg2)", [TSF],[AHT]) . The [TSF] and the [AHT] fields correspond to the interval data so I have 24 points per day. Traditionally I am looking at only 1 day at a time, though I'm able to expand this and look at the data for days, weeks and months at a time with ease.
The issue is when I pass a Null value. When volume is 0, then there is no handle time as nobody connected with a customer. When volume is 0 there is technically no TSF. It is not 0 and it is not 100% either.
I am able to get correct correlations returned when excluding null values OR when apply a filter for volume > 0. However this affects my chart and removes those intervals from the graph. It is important to myself and my end users to see all the periods on the graph because it is important to see the cumulative line for all periods.
1.)Find a way to tell R "Hey if this is null, ignore the entire row" - I don't want to ZN(AHT) or ZN(TSF), even though this does work its not accurate.
2.) Use SCRIPT_REAL("cor(.arg1, .arg2)", IFNULL([TSF], [Cumulative TSF]),IFNULL([AHT], [Cumulative AHT])) -
However this is again inaccurate but its less inaccurate than using 0s and/or 1. And it still would break if it was the first interval and I didnt have a value for the cumulative dataset yet.
3.) Create another workbook with the filter on as above. Somehow show the Correlation on this workbook and then superimpose this correlation onto my dashboard composed of several of the other graphs (I have a graph for AHT, TSF, Volume, etc). This is not ideal as I want everything in the tooltip if possible, so its hidden unless hovered or clicked over.