8 Replies Latest reply on Apr 21, 2017 12:33 AM by Brian Dudley

    Calculated Field with SUMIF based on certain months

    Madeline Coyne

      I am looking to create a calculated field in Tableau that mimics our bench-marking process. The difficulty is the benchmark time frames can change every month due to inaccurate data.

       

      For example, in the below January data was significantly skewed so Jan benchmark should the CpKPI for Jan and then we re-set starting in February. Therefore February should be Feb performance, March should be Feb and March, April should be Jan, Feb and March.

            

      MonthSpendKPICpKPIBenchmark TimeFrameBenchmark
      1-Jan$200,00050,000$4January$4
      1-Feb$300,0005,000$60February$60
      1-Mar$100,0002,000$50February+March$57.14
      1-Apr$1,000,00020,000$50February-April$51.85
      1-May$500,000200$2,500May$2,500
      1-Jun$50,0001,000$50February+March+April+June$51.79

       

      What I need is similar to an IF and SUMIF formula is excel... if jan sumif jan, if feb sumif feb if march sumif feb+march

       

      Does anyone have recommendations on how to create this formula?

       

      Thank you!

        • 1. Re: YTD Calc Excluding Certain Months
          Norbert Maijoor

          Hi Madeline,

           

          Not able to open your workbook incl data:( Please store your workbook incl. data as a .tbwx file in your thread.

          Upfront thanks a TON:)

           

          Regards,

          Norbert

          • 2. Re: YTD Calc Excluding Certain Months
            Norbert Maijoor

            Hi Madeline,

             

            Kicked off with a little example;) Not sure but find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

             

             

            Define Parameter

             

            April:

            if DATEPART('month',[Date])=1

            or DATEPART('month',[Date])=2

            or DATEPART('month',[Date])=4

            then [Sales] END

             

            Selection:

            if  [Month Selector]=#1-4-2017# then [April ]

            elseif datediff('year',[Date],[Month Selector])=0 and

            DATEDIFF('month',[Date],[Month Selector])>=0 then [Sales]

            END

             

            Regards,

            Norbert

            1 of 1 people found this helpful
            • 3. Re: YTD Calc Excluding Certain Months
              Madeline Coyne

              Hi Norbert,

               

              I updated the workbook to be an tbwx.

               

              I do not want to use a parameter for this calculation because it would not allow for consistent comparison to the KPI of that month. It needs to be a calculation along the lines of 'If month=1 then CPKPI elseif month=2  then avg(CPKPI) for month=1&2 elseif month=4 avg(CPKPI) for month=1&2&4

               

              Thank you,

              Madeline

              • 4. Re: YTD Calc Excluding Certain Months
                Norbert Maijoor

                Hi Madeline,

                 

                How would you like to visualize the data?

                 

                Regards,

                Norbert

                • 5. Re: YTD Calc Excluding Certain Months
                  Madeline Coyne

                  The data will be shown multiple ways. In monthly trended charts with a red, green, yellow indicator for if the CPKPI is above or below the CPKPI benchmark as well as in tables the show the CPKPI next to the CPKPI benchmark. Sometimes at a monthly level sometimes at a YTD level.

                   

                  We currently create this calculation in excel, but need to translate to tableau for automation.

                  • 6. Re: YTD Calc Excluding Certain Months
                    Brian Dudley

                    You can make a helper table to map which months to include in the calculations.

                     

                    This one uses month numbers, which means that dates have to be constructed.

                     

                      

                    ReportingMonthIncludeMonth
                    11
                    21
                    22
                    33
                    41
                    42
                    44

                     

                    Not sure what the rule for reporting March is, so in this case it just gets reported on its own.

                     

                    Join this with your data and you can report against the ReportingMonth and the proper months number will be included.

                     

                    Might be possible to construct one that only lists the excluded months.

                    • 7. Re: YTD Calc Excluding Certain Months
                      Madeline Coyne

                      is there anyway you can expand on this more with a sample workbook so I can better understand how to apply?

                      • 8. Re: Calculated Field with SUMIF based on certain months
                        Brian Dudley

                        The key to this is that the same record needs to be summed more than once -- i.e. Feb data needs to be in the Feb, Mar, and all subsequent month totals.

                         

                        Coming from a database background, my first thought is to use a join which is what I suggested above.

                         

                        The other way to accomplish this is with LOD calculations.

                         

                        So, this is possible:

                         

                        Case Month([Date])

                            When 1 Then {Fixed: Sum(IF Year(Date) = 2017 and Month(Date) = 1 Then [Sales] End)}

                            When 2 Then {Fixed: Sum(IF Year(Date) = 2017 and Month(Date) <= 2 Then [Sales] End)}

                            When 3 Then

                                {Fixed: Sum(IF Year(Date) = 2017 and Month(Date) = 2 Then [Sales] End)} +

                        {Fixed: Sum(IF Year(Date) = 2017 and Month([Date]) = 3 Then [Sales] End)}

                        End

                         

                        This is based on the workbook that Norbert Maijoor posted above.

                         

                        Hope this helps.