Please share your workbook and I can look into it.
From my understanding, your objective is to calculate weighted incident impact in days. Is this correct? If yes, then I'm wondering why a simple calculation will not suffice.
Something like this
[Weighted Impact in Days]
(DATEDIFF('minute',[Impact Start UTC],[Impact End UTC]) * [Impact Percentage])/1440
The result will be;
Is this the kind of result you are looking for?
Hope this helps.
No. That is exactly what I am trying not to do. Your calculation just adds up the weighted impact from each row.
If you look at my chart above, it might make more sense.
What I need to do is essentially sandwich down the impacts and take the highest weighted impact at any given time. So in the gantt chart above this is what I need to do.
During the first 15 mins, there are 2 incident impacts that have 50% impact. So their contribution is 7.5 mins. Then there is an impact that is for 39 mins that is 100% weighted.
So my total should be:
(15 mins * 0.5) / 1440 mins/day + (39 mins * 1.0)/1440 mins/day = 0.005208 + 0.027083 = 0.032292 days
your calculation includes and additional 54 mins weighted at 50% which is already overlapped by the other two incident impacts.
Thanks for the clarification. If I understood you correctly, the issue is that some CSO #s have overlapping incidents and you'd want to preferentially take the incident with the highest impact at any overlapping block of time.
I've looked at it and I'm not sure doing this in Tableau will be straightforward. I did manage to get correct results for some CSO #s but I believe that my approach is not robust enough and will fail depending on the number of incidents and how they are arranged. Merging and slicing time blocks (or "sandwiching down the impacts" as you call it) based on some impact percentage logic, in my opinion requires a good programming platform outside Tableau.
The screenshots below show the complexity of the task. For example, CSO # 4317 contains two overlapping incidents. The logic is to "sandwich down" the impact of the upper incident into the lower incident. The final weighted impact calculation written as;
(Duration A in days * 1.2%) + (Duration B in days * 30%) + (Duration C in days * 1.2%)
However, the issue becomes what happens when you have 3, 4, 5 or 10 incidents within a single CSO #?
My first thought will be that accomplishing such task will be tricky in Tableau. However, I'm curious to know if there is a way to achieve this which I've not thought about. So, I kindly ask Jonathan Drummey, Shinichiro Murakami or Shawn Wallwork to take a look if they have time.
I think That's too much complicated to try on Tableau.
Tableau is not a calculator like excel and it brings very complicated/fragile table calculation and delay of response.
But it looks like Ossai's chart and Karen's table is little bit different.
I am still strangling to understand Kare's requirement which has difference in red highlighted from my understanding.
Anyway,s weighted average on same start time is like this??
There are complicated table calc setting, but please refer the attached for that table calc,
SampleMetrics.twbx_SM_9.2.twbx 160.7 KB
Thanks Shin for looking at this. I also struggled to understand the requirement initially because it was not explained in the original question. However, Karen clarified her requirements in the subsequent comments.
So, the requirement is to calculate weighted incident impact in days per CSO. This is very straight forward when there is only one incident per CSO. It is also straight forward for CSOs with multiple incidents when all the incidents happened independently at different times. This will just be summing up all the weighted impact in days per incident. However, the complication is when there are multiple overlapping incidents with different impacts. Karen would like to calculate the weighted impact in days for the time range with the condition that the incident with the highest impact is considered in case of overlap. I believe the worksheet called "SLA mins rollup" is her desired summary result.
Using her example in the screenshots below for CSO # 4560 with 3 incidents;
There are 3 overlapping incidents for the full impact time range (54mins). There is an overlap between incidents IDS-4560a and INF-4560 in the first 14mins. Since both incidents have the same impact weighting of 0.5, either of them can be selected. The remaining 39mins have 2 overlapping incidents; IDS-4560b and INF-4560. IDS-4560b will be selected here because it has higher impact weighting of 1.0.
So total weighted impact in days will be (Duration A in days * impact weighting) + (Duration B in days * impact weighting) which is;
(14 mins * 0.5) + (39 mins * 1) = 46 mins = 0.032291 days
Another example is CSO # 4288 with multiple non-overlapping independent incidents. The total weighted impact in days is a simple sum of the weighted impact in days of all incidents; (EC2-4288a + EC2-4288b + EC2-4288c).
Another example is CSO # 4368 with multiple overlapping incidents. Here the total weighted impact in days is just the weighted impact in days for incident IDS-4368b because it has the highest weight and covers the entire impact time range. All other incidents are ignored.
There would be another approach to this, namely,
calculating the MAX of Impact Percentage
for every 1-minute time slot -- so one should have
a Datetime dimension of a minute granularity in the DS, --
then simply summing up those "weighted" minutes.
Please find the attached as a mockup.
SampleMetrics_YF.twbx 4.3 MB
1 of 1 people found this helpful
I’ve done this kind of calculation before in Tableauand it ends up being rather complicated and fragile to try to do it in Tableau with no additional data prep. What I ended up with was table calcs with a ton of LOOKUP() functions to compute how many minutes at what weighting would be added together where the data was sorted on the start date/time. Where this ran into problems was when given that data we wanted to do things like compute rates, grand totals, etc. on top of the table calculations and the nesting added a lot of additional complexity. So it’s do-able but quite challenging.
In general I’d go with what Yuri proposed and pad out the data to the desired level (minute, 5 minute, etc.), then the calculation can be done as a regular aggregate and not require table calculations and their complications.
Thanks Shin, Yuri and Jonathan for your contributions.
Thanks for all of the feedback. I had also previously tried to do what Yuri suggested and created a datasource that had a calendar sheet with every minute. I actually found a very nice post about it here that Jonathan commented on Re: The Advertising Campaigns Dashboard Conundrum
But since I have to do quarterly rollups of this data and show several quarters back, that meant that I ended up needing 91 days/qtr * 1440 mins/day = 131,040 rows of data per quarter. For 4 quarters, that would be 524,160 rows. So when I tried to do the table calculations, Tableau consumes gigabytes of memory.
I noticed that Yuri's datasource was structured/joined differently. Would that impact how long the calculations take and how much memory they consume?
But I will look at what Yuri did with his calcs and see if I can apply it to my non-simplified version and see if the calculation are still slow. I see that he used LOD and I didn't, so maybe his solution will be much faster.
To compare your (joined) schema with mine
the details of the left join in your schema
(those of the [Calendar] with the [Annotations] )
are needed to be revealed.
And don't forget about the Context filter in both my 'Sheet 7' views.
This filter is required to be applied across all views using this datasource
(one could even set it as a Datasource Filter when in production).
The combination of the cross-join (on 1=1) and the Context filter would effectively
left only those time slots (minutes) from the datetime dimension
for which the Impacts from the Events exist in the data.
So for the model dataset we've come up with about 3K rows in the result set
(by joining the original dataset with the 262K half-year [Datetime Dim]).
I'd expected a full-year date time dimension (twice as much) would be no problem.
The performance would depend mainly on the # of Impact Events and their lengths.