4 Replies Latest reply on Dec 9, 2015 8:21 AM by Jonathan Drummey

    Using Sumifs in tableau

    Sonam Mehta

      I am trying to figure out how to calculate a sumifs statement in tableau. In excel I have the this formula:

      =IFERROR((SUMIFS(Aspirin, PCICATH,1,DCDATE,">="&I3,DCDATE<"&M3))/(SUMIFS(Episode,AspirinContra,0,Death,0,Location,1,PCICATH,1,DCDATE,">="&I3,DCDATE,"<"&M3)),0)

       

      So in tableau I tried to recreate that but I didn't get the values I got in excel. I want to get percentages as well.

      My calculated field was:

      DischargeAspirinTop

      If [Aspirin}=1 and [PCI=1] and [Episode=1] then 1 else 0 end

       

      DischargeAspirinBottom

       

      If [Aspirin]!=2 and [DCStatus]=1 and [PCI=1] and [Location]!=3 and [Location]!=7 then 1 else 0 end

       

      Then to calculate DischargeAspirin:

       

      [DischargeAspirintop]/[DischargeAspirinbottom]

       

       

      this formula doesn't return percentages that excel does, so what am I doing wrong?

        • 1. Re: Using Sumifs in tableau

          Someone please answer this one.  I'm trying to do the same thing.

          Thanks!

          • 2. Re: Using Sumifs in tableau
            caitlin.donaldson

            Would it be possible to post a Tableau workbook and, possibly, an Excel document with the correct numbers?  If you are worried about releasing sensitive data, put fake numbers in the columns so we can see what it looks like.  That will help you get your question answered quicker.

            • 3. Re: Using Sumifs in tableau
              Bill Lyons

              I agree with caitlin.donaldson, an example packaged workbook would be greatly helpful. See Attaching a Packaged Workbook  and Anonymize your Tableau Package Data for Sharing |Tableau Support Community for more information. An excel file to accompany it would help as well.

               

              That said, I notice that your calculated fields do not include a SUM() function. Depending on how you use them, this may or may not be your issue.

              • 4. Re: Using Sumifs in tableau
                Jonathan Drummey

                I agree with Bill, a Tableau packaged workbook (.twbx) would be more helpful for us to be able to help you, along with a better description of the results that you're seeing so we know how they don't match with the % that you are expecting.

                 

                And things like this are common questions from new users, so here's  a first shot...try SUM([DischargeAspirintop])/SUM([DischargeAspirinbottom]) instead, then set the number formatting to percent***.

                 

                Here's why that might work: The calculations you wrote for DischargeAspirintop and DischargeAspirinbottom are computed for each record, returning 1 or 0. So [DischargeAspirintop]/[DischargeAspirinbottom] is also a record-level calculation, and depending on the results it is going to compute 0/1, 1/1, 1/0, or 0/0 and therefore return either 0, 1, or Null. Then when those results are brought into the view Tableau aggregates the measure based on that measure's aggregation (default is SUM()) to the grain of the view (based on the dimensions you have in the view). So you're probably seeing a number in the 10s, 100s, 1000s, etc. depending on how many records there are in the data. So in this case the sum is happening *after* the division.

                 

                The solution of wrapping each measure in SUM() to create SUM([DischargeAspirintop])/SUM([DischargeAspirinbottom]) then sums the numerator and denominator *before* the division, which is what you'd want.

                 

                *** the reason why you want to set the number formatting to percent is that if you are starting out with whole numbers then in some situations Tableau will display the result as a rounded whole number, so in that case you'd only be seeing 0 or 1 and not 14%, 58%, etc.

                 

                If that is the situation, then I suggest you watch Aggregation, Granularity, and Ratio Calculations | Tableau Software, it's an alternative explanation of what I just wrote.

                 

                Jonathan