7 Replies Latest reply on Mar 9, 2018 12:27 PM by Norbert Maijoor

# How to calculate Variance with prior 3 month average

Hi all,

I'm trying to calculate variance of cost from the 3 months prior to the selected month.

For example, if December 2016 is selected,

Variance with prior 3 months = [December Cost – (November Cost + October Cost + September Cost)/3] / December Cost

I'm using Tableau 10.1.4 and the workbook with sample data is attached. I'm used "L3M Cost" calculated field to calulate last 3 month sum, but it includes the selected month as well and doesn't start with the month prior to the selected month. Any help with this will be hugely appreciated.

• ###### 1. Re: How to calculate Variance with prior 3 month average

Hi Naveem,

Find my approach as reference below and stored in attached workbook version 10.1 located in the original threat.

Step1 : Defined custom date Month/Year

Step2 : Defined first day of the month

First day of the month: {fixed [Date Num (Month / Year)]:min([Date Num])}

Step 3: Defined parameter based on calculated field First day of the month:

Step 4: Defined the values per month

Amount selected month: if DATEDIFF('month',[Date Num],[Month Selector])=0 then[Amount] END

Amount selected month -1: if DATEDIFF('month',[Date Num],[Month Selector])=1 then[Amount] END

Amount selected month -2: if DATEDIFF('month',[Date Num],[Month Selector])=2 then[Amount] END

Amount selected month -3: if DATEDIFF('month',[Date Num],[Month Selector])=2 then[Amount] END

Step 5: Defined final requested calculation

Variance with prior 3 months: sum([Amount selected month])-(sum([Amount selected month -1])+sum([Amount selected month -2])+sum([Amount selected month -3]))/3/sum([Amount selected month])

Regards,

Norbert

• ###### 2. Re: How to calculate Variance with prior 3 month average

Thanks for the response Norbert. However, the values i get from your workbook are not correct. To give a better idea of the calculation, I'm attaching the data file with a sample calculation. Thanks again for the help.

• ###### 3. Re: How to calculate Variance with prior 3 month average

Hi Naveem,

Find my exact same approach on your provided dataset

1 of 1 people found this helpful
• ###### 4. Re: How to calculate Variance with prior 3 month average

Thanks a lot Norbert.

• ###### 5. Re: How to calculate Variance with prior 3 month average

HI Naveem,

You are welcome.

• ###### 6. Re: How to calculate Variance with prior 3 month average

Hello Norbert.  I am also trying to create a worksheet displaying the current month plus three prior months.  I found your posts and tried the calculations listed above but could not get them to work for me.

Screenshot 1:  Custom Date Field

Screenshot 2:  Month Selector Parameter (My data spans from 10/1/2017 - 1/31/2018).  I want to view the data as monthly totals.

Screenshot 3:  Error on first calculation.  Is it something in my calculation?  or my data?

• ###### 7. Re: How to calculate Variance with prior 3 month average

Hi Teresa,

Your Month/Year is a numeric field and not a date so de calc in datediff gives a error

Could you  share your workbook in .tbwx. format

Regards,

Norbert