2 Replies Latest reply on Apr 24, 2013 1:48 PM by arthi.keat

# Dynamically Pull Previous Years Sales Based on a Date from My Dataset

Hello All,

I am trying to solve a problem for a crosstab report I am building off of the Superstore Sales data that ships with Tableau.  I want to display every order, its order date, and sum(sales), ok fine and done.  Now I want a 4th column with shows me all of the previous years sales based on the Oder Date Year(Order Date)-1.  However I am having trouble building a calculation that does this.  My calculation is called "PreviousYearSales"

Any help would be appreciated.

• ###### 1. Re: Dynamically Pull Previous Years Sales Based on a Date from My Dataset

Hey All,

I got an answer by email I would like to share that shows a few more variations than just prior year:

You don’t have to use RawSQL to build these time-based-cohorts.  You can use IF statements to bucket a given measures into whatever dynamic time period you want. For example:

Sales Last Year

If datediff(‘year’, [Date], today())=1 then [Sales] end

Sales This Year

If datediff(‘year’, [Date], today())=0 then [Sales] end

Sales rolling 90 days

If datediff(‘day’, [Date], today())<=90 then [Sales] end

Sales last 4 weeks

If datediff(‘week’, [Date], today())<=4 then [Sales] end

Sales Prior YTD

if datediff('year', [Date], today()) = 1 AND datediff('day', [Date], today())>=365 then [Sales] end

1. ETC.

You can replace the condition with anything you want to act as a filter on a particular measure.  You can then write other calculated fields from these guys to find % change and other calculations.

• ###### 2. Re: Dynamically Pull Previous Years Sales Based on a Date from My Dataset

Hi David,

I am trying to implement the some thing similar in my workbook but I am lost. I am not sure where to start with. Can you please help.

I have attached the workbook and a desired graph which I am trying to achieve using Tableau. Would appreciate your time and effort in looking at the request.

Best Regards,

Arthi.