Show % Variance in Stacked Chart
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
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:
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:
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:
Next I created a second set looking at just the incidents that had arrival times of greater than 6 minutes:
Finally I am using the "Display Variance as" option of "# Value".
This gives me the results that I am expecting - namely, 2 met the benchmark in Q1 and 0 did in Q4:
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!
The same question
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:
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
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:
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
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
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
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
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.
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.
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
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
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
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
Hey Mike,
To get the minimum response time I simply use the "Aggregate -> Minimum" which gives me exactly what I want. The results given by the variance function is either: -33% or 33% depending on which value I use for v1 and v2. This makes sense according to the calculation presented.
Hey Mike,
To get the minimum response time I simply use the "Aggregate -> Minimum" which gives me exactly what I want. The results given by the variance function is either: -33% or 33% depending on which value I use for v1 and v2. This makes sense according to the calculation presented.
Hi Joshua,
I see you're using Minimum Aggregation in your earlier examples:
Then at some point, there's a seemingly new dimension field for these values:
The thing is, you can't use a Metric field here in the Variance:
So some kind of workaround must have been created to change this to a Dimension, so I'm wondering how you went about doing that.
I'm also a little unclear on the -33%/33% thing. Do you think you can just supply a screenshot of what you see when choosing that option as you've done for the Display Variance as # Value option?
Thanks,
Mike
Hi Joshua,
I see you're using Minimum Aggregation in your earlier examples:
Then at some point, there's a seemingly new dimension field for these values:
The thing is, you can't use a Metric field here in the Variance:
So some kind of workaround must have been created to change this to a Dimension, so I'm wondering how you went about doing that.
I'm also a little unclear on the -33%/33% thing. Do you think you can just supply a screenshot of what you see when choosing that option as you've done for the Display Variance as # Value option?
Thanks,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Thanks, Mike.
I changed my approach and it still doesn't seem to work quite right. I created calculated fields which now shows the correct value as a subtotal but now I can't figure out how to chart that number:
Thanks, Mike.
I changed my approach and it still doesn't seem to work quite right. I created calculated fields which now shows the correct value as a subtotal but now I can't figure out how to chart that number:
Hi Joshua,
Thanks for your response. It looks like those are sub total values for calendar quarter. You should be able to pull in those values to your chart if you drag in the Calendar Quarter and Max % Meets Objective fields into the Charts section, no?
If not, please provide the setup you currently are attempting in your Charts section.
Please note that there may be some delay in further response as we're on a skeleton crew until after the holidays. Also on that note... Happy Holidays Joshua!
Regards,
Mike
Hi Joshua,
Thanks for your response. It looks like those are sub total values for calendar quarter. You should be able to pull in those values to your chart if you drag in the Calendar Quarter and Max % Meets Objective fields into the Charts section, no?
If not, please provide the setup you currently are attempting in your Charts section.
Please note that there may be some delay in further response as we're on a skeleton crew until after the holidays. Also on that note... Happy Holidays Joshua!
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Joshua,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hey Mike,
Thanks for checking in. I haven't had a chance to test this yet as priorities have changed. Feel free to close this out and I can re-look at it when I get more time.
Thanks!
Josh
Hey Mike,
Thanks for checking in. I haven't had a chance to test this yet as priorities have changed. Feel free to close this out and I can re-look at it when I get more time.
Thanks!
Josh
Hi Joshua,
Sure, no problem. Please don't hesitate to reach back out on this or anything else! Hope you had a great New Year's!
Regards,
Mike
Hi Joshua,
Sure, no problem. Please don't hesitate to reach back out on this or anything else! Hope you had a great New Year's!
Regards,
Mike
Replies have been locked on this page!