2 Replies Latest reply on Aug 20, 2014 1:48 PM by Kapil Arora

    Calculation as sum of month than overall time frame

    Kapil Arora

      Hi,

       

      I’m a new user of Tableau.

      I was performing a table calculation and seek some advice to resolve issue.

       

      Background of problem:

      I’m facing challenge creating Total Breaks - which is sum of breaks for each month.

       

      Breaks are calculated in following manner: for every 3 client impact value for a month 1 break triggers.

      Example: month can have 0 breaks if SUM of client impact for that month is 0,1,2; Break count is 2 if sum of client impact value for month is 3,4,5; break count for month is 2 if sum of client impact is 6,7,8  and so on)

       

      Problem faced: I want to take sum of breaks for each month as total breaks and represent only total breaks on dashboard, however when I’m calculating breaks then breaks are calculated at the overall time period covered (not as sum of breaks for each month).

       

      Month

      Client Impact

      Break (client impact /3)

      January

      1

      0

      February

      6

      2

      March

      2

      0

      Total Breaks (should be)

       

      2

      Total Breaks (currently calculated )
      P.S: Probably calculation occurring is 9/3

       

      3


      In example above I just want to show '2 breaks' as output on my dashboard and I have already created a line chart to show the breakup of 'Breaks' for each month.

        • 1. Re: Calculation as sum of month than overall time frame
          edgarernesto.hernandez

          Hello

           

          I'm not sure if I understand well but I tried something.

           

          Basically, you will not be able to get an accurate total for the break value in a single table because as you observed, Tableau takes the whole sum of the client inpact and applies to it the calculation.

           

          You need to do it on another tab, using a window_sum formula and putting that together in a dashboard tab.

           

          You can find attached what I did. Basically you get the same result for calculating the number of breaks.

           

          The formula is :

          INT(SUM([Client Impact])/3) 
          

           

          as you want only values that are multiple of three

           

          Then on the other tab, where you want to calculate the total. You need to create a new table calculation with the following:

           

          IF FIRST()==0 THEN WINDOW_SUM([New Break]) ELSE NULL END
          

           

          Basically, that formula keeps the value for the whole table sum. Careful, you need to drag the month() field to the details section. And then to avoid having tableau show null values, I filter the new calculated field by specifying that he only shows non-null values.

           

          See attachment for the result.

           

          Hope it helps.

          • 2. Re: Calculation as sum of month than overall time frame
            Kapil Arora

            Thanks a lot for your guidance, it worked. You are a super star.