5 Replies Latest reply on Jul 9, 2018 11:26 AM by Anuvir Singh

# YOY Actual Values with an added dimension by month

I would like to create a calculation to get the actual of the previous year's (same month) value.  For instance, using the Tableau sales sample as shown below, the "Consumer" segment would have a value of \$6,928 for January 2015.

I've tried this fixed calculation with no success:  {FIXED [Segment]: SUM({FIXED MONTH([Order Date]), YEAR([Order Date])=2014 : SUM([Sales])})}

• ###### 1. Re: YOY Actual Values with an added dimension by month

If you are planning to continue to display the data in a table format as shown above, then you can use LOOKUP to get the desired values.

• ###### 2. Re: YOY Actual Values with an added dimension by month

Hari,

I'll need to display the data without the full window.  Best case, I can display only the selected month's actual and the YOY actual.

• ###### 3. Re: YOY Actual Values with an added dimension by month

Make 2 flags

Current Year flag

(

datepart('dayofyear',[Date])<=datepart('dayofyear',[TODAY])

and

datepart('month',[Date])<=datepart('month',([TODAY]))

and

datepart('year',[Date])=datepart('year',([TODAY]))

and

Previous Year flag

(

datepart('dayofyear',[Date])<=datepart('dayofyear',[TODAY])

and

datepart('month',[Date])<=(datepart('month',[TODAY]))

and

datepart('year',[Date])=(datepart('year',[TODAY])-1)

)

then

use this Calc

(SUM([Current Year flag])-SUM([Previous Year flag]))/SUM([Previous Year flag])

In your case you wont even need the dayofyear part. Just use the motn and year part of the calc.

• ###### 4. Re: YOY Actual Values with an added dimension by month

Thank you Anuvir, however, the workbook file seemed to fail on open.

Can you help a bit with where to apply the metric to the final calcuation?  I'm not seeing the metric applied anywhere in the calculations.

• ###### 5. Re: YOY Actual Values with an added dimension by month

You'll be able to open the workbook using the latest Tableau version.

here are the metric formulas i'm using

CY sales- (if [CY]=true then [Sales] end )

PY Sales- (if [CY]=true then [Sales] end )

and then

(SUM([CY sales])-SUM([PY sales]))/SUM([PY sales])