9 Replies Latest reply on Apr 12, 2013 10:52 AM by Elizabeth Navas

    Need to replicate the use of the sumif in Tableau

    Elizabeth Navas

      Hi

       

      I am trying to use the SUMIF condition from excel here in tableau to be able to sum the hours of all projects with a certain condition.  Here is the use case:

       

      ProjectHoursProject CPI
      13001.01
      22001.3
      33000.96
      41001.3
      51500.99
      66001.3

       

      I want tableau to calculate the sum of hours based on the CPI and 2 additional conditions:  Release Quarter and Site.

       

      If (Release Quarter = 'All' AND Site = 'All') then SUMIF (Project CPI range, CPI, Hours).  The result of this should be:

       

      Project CPISum Hours
      0.99150
      0.96300
      1.01300
      1.3900

       

      Scenario starts to complicate if the Release Quarter <> All and/or Site <> All because the SUMIF should sum only the hours for a specific CPI within the same RQ or Site respectively.

       

      Any idea how to do this?  I have tried to use calculated fields and table calculations and in addition I have the RQ and the Site as Quick Filters in context ... but I got stuck.  Could anyone help me to figure how to do this?

       

      Any help/ideas will be greatly appreciated!

       

      Thanks!

      Elizabeth

        • 1. Re: Need to replicate the use of the sumif in Tableau
          Dana Withers

          Hi Elizabeth,

           

          Not sure if I understand your problem...

          Based on the first picture, the second one can be created without any specific calculations by dragging Project CPI into rows and hours into the show me/text shelf, which will sum it automatically. If you then filter on the other two columns, wouldn't that work?

          Or does your data not look like the first image? Perhaps it is more clear with some sample/dummy data?

           

          Thanks!

           

          Dana

          • 2. Re: Need to replicate the use of the sumif in Tableau
            Elizabeth Navas

            Hi Dana,

             

            Thanks so much for replying.  However, your recommendation is not giving me the total sum for each CPI depending on the filters selected.  I want the Y Axis to show me the SUM of Actual MH for each CPI depending on the RQ and/or Site selected.  Here is the result by using your recommendation:

             

             

            Thanks again!  Please let me know if you need more data/clarification.

             

            Elizabeth

            • 3. Re: Need to replicate the use of the sumif in Tableau
              Dana Withers

              Hi Elizabeth,

               

              Thanks for the screenshot - that does help to make things clearer, but I'm still not sure if I fully understand the screenshot you're showing vs what you describe.

              From what I can see, you have filtered on one particular RQ and one particular Site (filter shelf in grey). Then you've further filtered to one particular CPI (filter shelf in blue). The graph is showing you the sum total of Actual MH (as per rows shelf) for each unique combination of Program, Project, RQ and Site (because they're on your level of detail shelf - there seem to be two combinations of them, which is why you get two points).

              What are you expecting to see?

              Why do you have Program, Project, RQ and Site on your level of details shelf?

               

              From what I understand that you described initially you want to do the following:

              See sum total of Actual MH for each CPI, optionally grouped/filtered by RQ and/or Site.

              To do that I'd put Actual MH in the rows (as you have done), CPI in the columns (as you have done as well). Clear the Level of Detail box of all it's contents, because I don't think there is a reason to include it (I may of course be wrong if any calculation on the sheet uses those fields, but that is not what you described I think and none of the fields you are using/have mentioned seem to be calculations at all). Then you can filter, group (using column shelf) or colour as per RQ and/or Site.

               

              Could it be that in the current screenshot you have two dots instead of one in your graph because there are either two programs or two projects matching the filters you have selected?

               

              Hope this helps!

               

              Dana

              • 4. Re: Need to replicate the use of the sumif in Tableau
                Elizabeth Navas

                Hi again

                 

                We are trying to calculate the sum of hours for all projects with equal CPI.  I have attached the excel file.  Please use the Consolidated tab for the raw data and the GPO tab for the graphic and then look for SUM Hours (You will need to expand some columns to find it).

                 

                The SUMIF translation in few words is this:

                 

                If (RQ and Site and Program) = All

                   Then sum (CPI  range, CPI, Actual MH range)

                If (RQ <> All and Site=All and Program=All)

                   Then sum (Actual MH range, CPI range, CPI, RQ range, RQ)

                If (RQ <> All, Site <> All, Program = All)

                   Then sum (Actual MH range, CPI range, CPI, RQ range, RQ, Site range, Site)

                If (RQ <> All, Site <> All, Program <> All)

                   Then sum (Actual MH range, CPI Range, CPI, RQ range, RQ, Site range, Site, Program range, Program)

                If (RQ = All, Site <> All, Program = All)

                   Then sum (Actual MH range, CPI range, CPI, Site range, Site)

                   Then sum (Actual MH range, CPI range, CPI, Site range, Site, Program range, Program)

                 

                I added some of the fields to the level of detail for the tooltip but if you think it might be causing problems, I will remove it.  I have added some of them as well to Context to help me with the calculations.  CPI should not be one of the filters at the end.  I have been using it to verify the final values from the excel spreadsheet.

                 

                Thanks in advance for your help

                • 5. Re: Need to replicate the use of the sumif in Tableau
                  Dana Withers

                  Hi Elizabeth,

                   

                  Wow that SumIf looks massively complicated. To be honest I'm either missing the point there or it is more complicated than it needs to be for Tableau (different for Excel of course). If you have data in Tableau and calculate a sum total of stuff... like hours and then filter it... the sum is automatically done for the filters. What you filter out will not be counted in the calculation.

                  So when I put the workbook together by simply dragging in the hours field and letting it total, and then I filter, I get the same values as I can see in your excel sheet (thanks for adding that btw - it was really helpful to just play with the data).

                  Percentage of total does the same: it sees the "total" as "everything you have not filtered out". Again that gives me the same numbers as your sheet.

                   

                  It looks slightly different because the way axis values are chosen is a little different from Excel (I mean different ranges than excel would pick), but I verified the numbers and they look the same.

                   

                  I made it in version 8 first because we just moved to that, but then realised from your screenshot you're probably on 7 still. The dashboards look better in V8 if you have it

                   

                  Can you please verify the data and see if you can see if it does something you don't expect it to do?

                   

                  I've not looked into the tooltip yet - do let me know what you want in there that required the level of detail to be used.

                   

                  Looking forward to your reply hahaha we'll get it sorted at some point...

                  • 6. Re: Need to replicate the use of the sumif in Tableau
                    Elizabeth Navas

                    Thank you very much!  You are the best!  I will take a look and will let you know if I have any questions. 

                    One million thanks again!

                    Elizabeth

                    • 7. Re: Need to replicate the use of the sumif in Tableau
                      Elizabeth Navas

                      HI Dana,

                       

                      I was able to follow your recommendations and my tableau dashboard is almost ready.  However, I am not sure how to calculate the Weighted Average and the Delta because again, these formulas are coming from Excel  (they are using the SUMPRODUCT formula).  Please check the GPO tab in the original excel file for the details.  In addition, I had trouble trying to accommodate the dual axis and now it is showing the Actual MH on the right!  Could you please help me to understand what happened?  If I accommodate the values on the Rows shelf to make the SUM (Actual MH) to be listed first, the two marks (bars in grey and black) are not displayed    Finally, I want to add the Project Name to the tooltip, but if I add it to the level of detail shelf, it messes up the calculations.  If I try to edit the tooltip, the dimension is not listed in the insert drop down list.

                       

                      Thanks again for your help

                      • 8. Re: Need to replicate the use of the sumif in Tableau
                        Dana Withers

                        Hi Elizabeth,

                         

                        You can change the left and right axis by dragging the pills. If I drag the pill for the right axis to the left of the other one (make sure you see the little blue triangle to indicate it will go to the left) they will change around without any issues. If you right-click on the axis you have an option to "Move Marks to Front" or "Move Marks to Back". Since you have two bars on top of each other, you need to move the smaller one to the front.

                         

                        I just added Project name to the CIP - Hours Distribution chart and nothing seemed to change. So not sure what you mean there. If there is a one-on-one relationship with what you've already calculated it shouldnt make a difference. If each bar in the chart could be multiple project names, then I'd recommend rethinking the requirement.

                         

                        The weighted average was nearly right, but needed a SUM in there, to make sure you use the totals for the group - ie the sum of hours*cpi divided by the sum of hours:

                        sum(([Actual MH]*[CPI]))/sum([Actual MH])

                         

                        As for the Delta... I got a bit lost there in the calculations and things are a bit busier now, so I'm not sure when I have time to look at this

                         

                        Sorry it took a while to get back to you... hope it helps,

                         

                        Dana

                        • 9. Re: Need to replicate the use of the sumif in Tableau
                          Elizabeth Navas

                          Hi Dana,

                           

                          Thank you very much for helping me with this.  I was able to  fix the issue with the axis!  Thank you!  For the project, what i need is the Project Name listed in the tooltip when the user selects an specific value.  I inserted the Project Name in the Level of Detail shelf but by doing that, the calculations get messed up.  What would you recommend me to be able to see the Project Name in the dashboard? 

                           

                          With regards the Delta, I figured out the formula, I guess.... but then when I try to calculate the WAV Delta and the WAV CV things get worse because I am trying to use mix fields:  aggregated and non-aggregated.  This is what I used for the Delta:  ABS([Weighted Average]-AVG([CPI]))

                           

                          To make easier for you to understand the WAV, the delta, the WAV Delta and the WAV CV, here is the summary of the formulas in my own words .... thanks again for your help!!

                           

                          Weighted Average CPI

                          1)  (Actual MH * CPI) = Weight CPI

                          2)  SUM(Weight CPI)

                          3)  SUM(Actual MH)

                          4)  Weighted Average CPI = SUM(Weight CPI)/SUM(Actual MH)

                          In Tableau:  SUM(([Actual MH]*[CPI]))/SUM([Actual MH])  This is done, working OK

                           

                          Delta

                          If Percentage of %Total Actual MH = 0 then " "

                          else ABS(Weighted Average CPI-CPI)  

                          end

                          Note:  Since we are mixing aggregate and non-aggregate, I tried to make the CPI aggregated by adding the AVG.

                          In Tableau: ABS([Weighted Average CPI]-AVG([CPI])).  Not sure if the formula is correct.  Please confirm.

                           

                          Weighted Average Delta and Weighted Average CV

                          1)  (Actual MH * Delta) = Weight Delta

                          2)  SUM(Weight Delta)

                          3)  SUM(Actual MH)

                          4)  Weighted Average Delta = SUM(Weight Delta)/SUM(Actual MH)

                          5)  Weighted Average CV = Weighted Average Delta/Weighted Average CPI

                          In Tableau:  To be determined ....????

                           

                          I know that you are busy but whenever you have a chance, please review these formulas and let me know if you need  any further clarification.  These are the last 3 formulas in my entire dashboard. 

                           

                          Thanks in advance for your help!