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

# Calculated Field with SUMIF based on certain months

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.

 Month Spend KPI CpKPI Benchmark TimeFrame Benchmark 1-Jan \$200,000 50,000 \$4 January \$4 1-Feb \$300,000 5,000 \$60 February \$60 1-Mar \$100,000 2,000 \$50 February+March \$57.14 1-Apr \$1,000,000 20,000 \$50 February-April \$51.85 1-May \$500,000 200 \$2,500 May \$2,500 1-Jun \$50,000 1,000 \$50 February+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

Upfront thanks a TON:)

Regards,

Norbert

• ###### 2. Re: YTD Calc Excluding Certain Months

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

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,

• ###### 4. Re: YTD Calc Excluding Certain Months

How would you like to visualize the data?

Regards,

Norbert

• ###### 5. Re: YTD Calc Excluding Certain Months

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

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.

 ReportingMonth IncludeMonth 1 1 2 1 2 2 3 3 4 1 4 2 4 4

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

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

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.