14 Replies Latest reply on May 16, 2014 5:57 AM by Don Barnetson

# Calculate Previous Years Sales

Hi, hope you can help

I have a Sales Calculation for current year and previous year sales.

The problem I am experiencing is that no matter what calculation I use, I'm always getting this Financial years Sales figures.

I have a filter on the data so that it only shows FY2014, but then I want to create a calculation that shows the % To last years revenue + an uplift of a dynamic paramater (e.g  FY2013 Sales + 20% = 2014 Target)

I've tried using this:

If datediff('year', [Previous revenue_period], [revenue_period])=1 then [rev] end

But this just returns the sales for the current Financial year.

Any ideas on the best way to do this? I've had a search through the KB and forums.

Thank you!

• ###### 1. Re: Calculate Previous Years Sales

Like this?

N.

• ###### 2. Re: Calculate Previous Years Sales

This approach is useful in MANY scenarios--basically, Noah has used a Table Calc version of the "Year" filter, to effectively filter the data without impacting the results.  So, he is able to display the previous year in the view, while retaining the underlying data that allows you to compare this year to the previous.

When you apply a typical YEAR filter of 2014 to the view, Tableau has no way to compute the previous year's values.  However, since Table Calc filters happen subsequent to the data being pulled into the view and other filtering in the view, you can effectively accomplish what you want as shown by Noah!

A Jedi (Filter and Table Calc) Trick | Tableau Software

Maintain percent of total in Tableau when using filters

Noah, please feel free to correct me on anything that is not clear or may be confusing to the OP.  I would like to get better at describing what is happening in Tableau, wherever possible, as I feel this is something I can practice that will help improve my abilities on the whole.

• ###### 3. Re: Calculate Previous Years Sales

Right, technically all calcs happen after the data is pulled in (except those in custom sql), but Table calcs happen before filters based on Table calcs, so this is a sort of token table calc whose only purpose is to push the order of operations down a bit. Otherwise, the data we need is gone before Tableau sees it.

Note though, while this solves a problem here, it can create some as well. If we had a grand total for example it would show the unfiltered total, so we'd need to be a bit more sneaky in that case. In that case blending on a duplicate datasource with the year shifted might be a way to go.

N.

• ###### 4. Re: Calculate Previous Years Sales

Thanks again, Noah!

• ###### 5. Re: Calculate Previous Years Sales

Hi Noah,

I'm curious, what do you mean by "...technically all calcs happen after the data is pulled in (except those in custom sql),"? Particularly the "all"?

Looking at the queries Tableau issues, my observation has been that Tableau computes as much of the row level and aggregate calculations as possible in the query(ies) to the data source(s). What appear to be solely computed inside Tableau are row-level calcs using USERNAME and USERGROUP, ATTR(), calcs using fields from other data sources, and table calcs.

Jonathan

• ###### 6. Re: Calculate Previous Years Sales

Good point Jonathan, thanks for calling me out on this. I was just thinking: you cain't compute with what ya ain't got... but obviously things aren't that simple. ****, even that statement isn't true, I of all people should know we can create complex data from almost nothing... I'll try to avoid using absolutes for at least 72 hours, no promises though.

N.

• ###### 7. Re: Calculate Previous Years Sales

Hi Frances,

I may be over simplifying this, but from what I'm guessing this example is what I think you're looking for:

• ###### 8. Re: Calculate Previous Years Sales

Hi

This is 90% what I’m looking for ☺ I can get this to drive off of a parameter and continue to use 2 years of data. My only modification question that I have to this is, can I get the Field name to be parameter based? E.g. I call my calculation Current Year, and have it equal to the year I am looking for, but in the report, it shows up as being Sales 2014?

Thanks

Fran

• ###### 9. Re: Calculate Previous Years Sales

Yep. Err... No actually, but if I'm understanding the question there is a workaround. Just to be sure it wasn't something else, I went ahead and setup everything I could think of to be parameter controlled.

N.

• ###### 10. Re: Calculate Previous Years Sales

So, you mean something like this....?

I've added a dynamic 'Current Year Parameter', as well as removing the headers replacing them with a text box (This can only been done as a dashboard). This allows you to insert results of your Parameter selection.

• ###### 11. Re: Calculate Previous Years Sales

That’s the one ☺

Thought you did it on the report :P

Thank you so much!

• ###### 12. Re: Calculate Previous Years Sales

This is an additional question, but I had to do things a little differently to what you proposed.

My Target calculation looks like this:

if sum(YEAR(DATEADD('month',6,[revenue_period])))= sum(()-1) THEN SUM(()+(*)) END

Because I’m using a year and converting it to the float using the sum (because my financial year is July, not January).

So I’m trying to calculate the % Difference between the target and current year which is just returning 0 because my Target is FY 2011 and my current Year is 2012.

Know any way around this?