1 Reply Latest reply on Jul 31, 2012 2:14 PM by Tracy Rodgers

    sumproduct at row level?

      I’m trying to perform a table calculation which will sum the number of outcomes missed from the granular level of detail of incident number to the overall level of Area and service.

       

      The data works as 1 incident can have many outcomes missed, this outcome will have 1 area and service associated with it; however the same outcome can be missed across more than 1 incident and the number missed can be from 1 to infinity.

       

      At present my data is set up with other information so for one outcome there can many rows with different additional data, albeit the outcomes information is a repeat. 

       

      I am trying to show the number of outcomes missed at each level but the way my calculation works at the minute cannot cope with instances where more than 1 outcome was missed. In excel I would utilise the sumproduct function but I have not found a way to replicate this for the table calc.

       

      Attached is the Tableau workbook with the calculation as I currently have it (which only works at the outcome level) and also attached is an excel workbook showing the way I would calculate the measure needed using excel (calcs at row level on sheet 'Outcomes_Data' then aggregated using the pivots tables on sheet 'Outcomes Data Required'). The main issue I think I have is trying to mix aggregate and non-aggregate measures.

       

      The calculation results for number of outcomes must always be a whole number - that's how I have spotted there is an error in the calculation.

       

      Any help on this would be really appreciated as I have been struggling for some time with this!

       

      Thanks

       

      Katy

        • 1. Re: sumproduct at row level?
          Tracy Rodgers

          Hi Katherine,

           

          The calculation is doing what I would expect. However, one way around this is to create the following calculation:

           

          IF FIRST()==0 THEN

          WINDOW_SUM(([incident_level_outcomes_missed]),0,IIF(FIRST()==0,LAST(),0))

          END

           

          Then, create a duplicate of Service--right click on it in the data box and select Duplicate--and place this copy on the level of detail shelf. Then, place the calculation on the by Area sheet. Right click on it and select Compute Using-->Service (Copy).

           

          Hope this helps!

           

          -Tracy