Show % Variance in Stacked Chart

Joshua Baron shared this question 1 week ago
Awaiting Reply

I am creating a report that shows the response time of our fire department to incidents. More specifically I want to compare the response time of the first arriving unit to a benchmark. I have isolated the data set to an example that illustrates this. First thing I did was calculate the response times for all units per incident/XWIpssECBAgQIAAgZICCriS+nITIECAAAECBDIEFHAZaEIIECBAgAABAiUFFHAl9eUmQIAAAQIECGQIKOAy0IQQIECAAAECBEoKKOBK6stNgAABAgQIEMgQUMBloAkhQIAAAQIECJQUUMCV1JebAAECBAgQIJAhoIDLQBNCgAABAgQIECgpoIArqS83AQIECBAgQCBDQAGXgSaEAAECBAgQIFBSQAFXUl9uAgQIECBAgECGgAIuA00IAQIECBAgQKCkgAKupL7cBAgQIECAAIEMAQVcBpoQAgQIECBAgEBJAQVcSX25CRAgQIAAAQIZAgq4DDQhBAgQIECAAIGSAgq4kvpyEyBAgAABAgQyBBRwGWhCCBAgQIAAAQIlBRRwJfXlJkCAAAECBAhkCCjgMtCEECBAgAABAgRKCijgSurLTYAAAQIECBDIEFDAZaAJIUCAAAECBAiUFFDAldSXmwABAgQIECCQIaCAy0ATQoAAAQIECBAoKaCAK6kvNwECBAgQIEAgQ0ABl4EmhAABAgQIECBQUkABV1JfbgIECBAgQIBAhoACLgNNCAECBAgQIECgpIACrqS+3AQIECBAgACBDAEFXAaaEAIECBAgQIBASQEFXEl9uQkQIECAAAECGQIKuAw0IQQIECBAgACBkgIKuJL6chMgQIAAAQIEMgQUcBloQggQIECAAAECJQUUcCX15SZAgAABAgQIZAgo4DLQhBAgQIAAAQIESgoo4Erqy02AAAECBAgQyBBQwGWgCSFAgAABAgQIlBRQwJXUl5sAAQIECBAgkCGggMtAE0KAAAECBAgQKCmggCupLzcBAgQIECBAIENAAZeBJoQAAQIECBAgUFJAAVdSX24CBAgQIECAQIaAAi4DTQgBAgQIECBAoKSAAq6kvtwECBAgQIAAgQwBBVwGmhACBAgQIECAQEkBBVxJfbkJECBAgAABAhkCCrgMNCEECBAgQIAAgZICvwNNcN6Gn2gq5gAAAABJRU5ErkJggg==

I have circled the fastest response times for each incident for clarity. The next thing I did was to use the Aggregation -> Minimum on the response time which led to the following:

/Yun0Xoj4ZqWhsE9x6wsalqznSmvn0bezlDopwQkIAGfC2i82+fEykACEpCABCQgAQl4V0ABnHc9lZoEJCABCUhAAhLwuYACOJ8TKwMJSEACEpCABCTgXQEFcN71VGoSkIAEJCABCUjA5wIK4HxOrAwkIAEJSEACEpCAdwUUwHnXU6lJQAISkIAEJCABnwsogPM5sTKQgAQkIAEJSEAC3hVQAOddT6UmAQlIQAISkIAEfC6gAM7nxMpAAhKQgAQkIAEJeFdAAZx3PZWaBCQgAQlIQAIS8LmAAjifEysDCUhAAhKQgAQk4F0BBXDe9VRqEpCABCQgAQlIwOcCCuB8TqwMJCABCUhAAhKQgHcFFMB511OpSUACEpCABCQgAZ8LKIDzObEykIAEJCABCUhAAt4VUADnXU+lJgEJSEACEpCABHwuoADO58TKQAISkIAEJCABCXhXQAGcdz2VmgQkIAEJSEACEvC5gAI4nxMrAwlIQAISkIAEJOBdAQVw3vVUahKQgAQkIAEJSMDnAgrgfE6sDCQgAQlIQAISkIB3Bf4f6Jx9paAG7sIAAAAASUVORK5CYII=

This is exactly what I would expect - far so good. Now I want to show the % of the incident responses by year and quarter (cols 1 and 2 above) that met our objective (6 minutes). Based on the above, you can see that 2/3 or 66% met the objective for Q1 of 2017 and 0/1 or 0% met the objective for Q4 of 2017. Poking around a bit, I see that I can use a Variance function in the charting area which I would think gives me the info I need. So I added a record counter to each record that would give me the total:

/x8fFPy3P4z2kQAAAABJRU5ErkJggg==

Next, I go to the chart area to create a vertical column chart. On the chart, I add "Quarter" to the horizontal axis, and create a Variance for the vertical axis. For my variance, I create two custom sets - the first being the total record count as follows:

/8DU6atumkGs6AAAAAASUVORK5CYII=

Next I created a second set looking at just the incidents that had arrival times of greater than 6 minutes:

/TVkAAAAAElFTkSuQmCC

Finally I am using the "Display Variance as" option of "# Value".

/wHwdHdvOrtEbAAAAABJRU5ErkJggg==

This gives me the results that I am expecting - namely, 2 met the benchmark in Q1 and 0 did in Q4:

/AQLWwaep9r62AAAAAElFTkSuQmCC

Now finally, I want to show this number as a %. In looking at the "Display Variance as" options, none of the formulas would give me the result I would expect. It should read 66% for Q1 and 0% for Q4. The closest option I think is % Percentage, but that formula ((v1-v2)/v2) for Q1 yields:

(3-1)/1. It would seem to me that the function should be ((v1-v2)/v1) which would give me: ((3-1)/3 = 66%. Any help would be appreicated - thanks!

Comments (6)

photo
1

Hi Joshua,

Thanks for reaching out. Generally, Support doesn't deal with specific data-related report creation questions, this would fall more in the realm of consulting services. Support is here to offer general guidance and investigate, log, and troubleshoot potential defective behavior. That said, we're always happy to give some pointers when we can.

In this case, I'm a bit confused in what you're looking to do here. If I understand correctly, you're looking to create another chart as the one above, except instead of showing '2' and '0' you want '66%' and '0%'? If that's correct, much like in our other case, I suspect the easiest way to achieve this result would likely be by scripting Calculated Fields if % of Total Variance isn't giving you the result you're looking for, the exact scripting of which I'm afraid we can't really assist with, but as one small example, you can kind of get the idea of what should be scripted by looking what the Variance field itself is doing. For example, using your second set, you could do something like CASE WHEN Min Response Time 6 THEN 1 ELSE 0 END:

/LlfHAAAAAElFTkSuQmCC

And work from there. Please try doing this using Calculated Fields and let me know if you have any questions while doing so.

Regards,

Mike

photo
1

Hi Joshua,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Joshua,

I hope my suggestion gave you what you're looking for! I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to assist further.

Regards,

Mike

photo
1

Hey Mike,

I'm sorry - I meant to respond last week and I got busy. Unfortunately it did not help me. And to your original point about this falling into consulting services, I would disagree slightly. My original thought was that the formula for Variance does not seem correct. As I stated above, Yellowfin states the formula for variance as ((v1-v2)/v2). This would seem to always provide the negative of what I was expecting. I think what I am expecting is the change between v1 and v2 and what the Yellowfin formula provides me is the difference. So for example, if I use the following: v1 = 1; v2 = 2, then I would expect the % change to be 100% - or (2-1)/1. But what Yellowfin provides me is the difference between the two which is -50% or (1-2)/2 - meaning v1 is 50% smaller than v2 - which it is, but it's not the value I am looking for. I will keep playing with calculated fields and see if I can make it work.

photo
1

Hi Joshua,

No worries! Thanks for the response.

Unfortunately, testing indicates that Functions within the Charts section of reports does not generate any SQL Statement that we can investigate to see what math is being performed, so I'll have to try and mimic your setup to replicate this with our own data set. I'm approaching end-of-day in a few minutes though, so I'll update you with my findings on this tomorrow morning/early afternoon.

Regards,

Mike

photo
1

Hi Joshua,

I've gotten this mostly setup, but I have a couple further questions:

How did you go about changing your Min. Aggregated Metric for Response Time to a Min Response Time dimension?

When you do use the % Percentage option for Variance, what are the results? You said you expect 66% and 0, but what are you currently seeing?

Thanks,

Mike