Create a graph with three values in which two of them depend on the maxium of the other

have a table with four columns:

  • The date truncated to the minute
  • The maximun number of concurrent calls of this minute
  • For this calls, which are inbound calls
  • For this calls, which are outbound calls

keep in mind that Max concurrent calls=inbound+outbound

I want to plot on the same graph the maximum number of concurrent calls with their respective number of incoming and outgoing calls with an option to change the aggregation.

The problem comes when I try to change the aggregation. Instead of having the maximun number of calls and their respective number of incoming and outgoing calls, I plot the maximun number of calls and the maximun number of outbound and inbound calls of this period.

For instance, If we plot using the data of the example table, the datapoints that I expect are 4 max concurrent calss, 4 inbound calls and 0 outbound calls. However, I get 4 max concurrent calss, 4 inbound and 2 outbound, because the maximun number of outbound calls in this period of time is 2.

So, How can I plot the inbound and outbound of the maximun number of calls? I cannot see the option to graph columns depending of the values of others, and I am not able to create new fields that resolve this problem

This is the plot that I want:


Im using the maximun for all values.The light blue line is the maximun number of calls,the orange is the inbounds and the blue is the outbounds. The first one is the sum of inbounds and outbounds. However, if I change the granularity to hours:


Now the light blue light line is not the some of the others(13+26 is 39,not 38).To get the number of outbounds and inbounds calls when the total calls are maximun, Im trying something like this:

ifelse({Total calls}=max({Total calls}),{Inbound calls},0)

In order to get the inbounds calls when the total calls are maximun. However Im getting mismatched aggregation error.

Hi,

Thank you for reaching out. You indicated your data is stored by minute. Are you trying to show the data at the hour level in the chart?

Best,
Sean

Hi,

Yes, I want the value of inbounds and outbounds calls when the maximun number of total calls are maximun.

For example, if we have:

At 10:10: 10 outbounds calls and 2 Inbound calls (12 total calls)
At: 10:20: 5 outbound calls and 10 inbound calls (15 total calls)

I want to get 5 outbound calls, 10 inbound calls and 15 total calls in that hour. Instead, Im getting 10 outbound calls because is the maximun number of outbound calls in that range (Im getting also 10 inbound calls and 15 total calls).

Regards

Hi @estaraja -

If I understand correctly. You are trying to retrieve 1 record for every hour. The 1 record is the highest max_concurrent_calls value.

Have you thought about how you want to handle ties? Example below.

You could use this approach. Using a LAC-W Rank function.

c_max_num_concurrent_rn

// rank rows within each hour by max_num_concurrent, highest to lowest.
// TODO: how to handle ties
rank([{max_num_concurrent} DESC], [truncDate("HH",Date)], PRE_AGG)

Result (ties not dealt with).