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

    David Spezia

      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
          David Spezia

          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
            arthi.keat

            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.