12 Replies Latest reply on Jun 13, 2017 2:45 PM by Yuriy Fal

    Running sum of weighted impact mins

    Karen Cook

      I'm working on reports for incident management and need to get a running total of the weighted impact minutes for a given period considering multiple incident impacts.  I was able to get a running total of duration minutes, but I also need to factor in a weighing factor for each incident impact.  For example, an impact duration might have been 14 minutes, but it has a weighting factor of .5, so the impact minutes would be 7 minutes.  Here is an example:

       

      For this example, the result should be (15 mins * 0.5) + (39 mins * 1) = 46 mins = 0.032292 days

       

      I'm using this calculation to step through the incident impacts:

       

       

       

       

       

      Unfortunately, my calculation is essentially giving me the result for the incident impact as (54 mins * 0.5) = 27 mins = 0.01875 days

       

      Any suggestions for how I might be able to properly calculate this?

       

      Message was edited by: Karen Cook I've attached an anonymized version of my workbook.

        • 1. Re: Running sum of weighted impact mins
          Hari Ankem

          Please share your workbook and I can look into it.

          • 2. Re: Running sum of weighted impact mins
            Okechukwu Ossai

            Hi Karen,

             

            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.

            Ossai

            • 3. Re: Running sum of weighted impact mins
              Karen Cook

              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.

              • 4. Re: Running sum of weighted impact mins
                Okechukwu Ossai

                Hi Karen,

                 

                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.

                 

                 

                Ossai

                 

                 

                • 5. Re: Running sum of weighted impact mins
                  Shinichiro Murakami

                  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.

                   

                   

                  Thanks,

                  Shin

                  • 6. Re: Running sum of weighted impact mins
                    Shinichiro Murakami

                    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,

                     

                    Thanks,

                    Shin

                    1 of 1 people found this helpful
                    • 7. Re: Running sum of weighted impact mins
                      Okechukwu Ossai

                      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.

                      • 8. Re: Running sum of weighted impact mins
                        Yuriy Fal

                        Hi friends,

                         

                        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.

                         

                        Yours,

                        Yuri

                        1 of 1 people found this helpful
                        • 9. Re: Running sum of weighted impact mins
                          Jonathan Drummey

                          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.

                           

                          Jonathan

                          1 of 1 people found this helpful
                          • 10. Re: Running sum of weighted impact mins
                            Okechukwu Ossai

                            Thanks Shin, Yuri and Jonathan for your contributions.

                            • 11. Re: Running sum of weighted impact mins
                              Karen Cook

                              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. 

                              • 12. Re: Running sum of weighted impact mins
                                Yuriy Fal

                                Hi Karen,

                                 

                                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.

                                 

                                Yours,

                                Yuri