6 Replies Latest reply on Jan 17, 2014 12:29 PM by Lisa Tango

    Show sales for selected date range and selected date range minus 1 year

    john.finn

      Hi,

       

      I have been searching all day for a solution and I have downloaded some samples but I cannot solve my issue. I am fairly new to Tableau so apologies if I have missed something obvious...

       

      I want to automatically display sales (or QTY sold) for the user selected date range minus 1 year. So if use selects a date range 01/01/2013 to 31/01/2013 (01/31/2013 US format) then I want a second column to automatically display sales value for 01/01/2012 to 31/01/2012. The selected date range is dynamic and cannot be fixed and the comparison value will need to change as the user changes the selected date range.

       

      Thanks in advance.

        • 1. Re: Show sales for selected date range and selected date range minus 1 year
          Tracy Rodgers

          Hi John,

           

          You'll first need to create a date parameter (or 2 if you want a range). Then, create a calculated field similar to the following:

           

          if [Date Field]<=DATEADD('year', -1, [date parameter])  then [Date Field] end

           

          Then, place this on the filter shelf and select True. Right click on the parameter and select Show Parameter Control.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Re: Show sales for selected date range and selected date range minus 1 year
            john.finn

            Hi Tracy,

             

            Thanks for your reply. I have only gotten access to the historical sales today so I am only looking at this now.

            I have to admit I am utterly confused and unable to achieve what I need.

             

            To confirm, I am trying to show sales for example department by store. The user selects a date range (e.g. 1st Nov 13 to 30th Nov 13) and the report will show sales for this date range AND also the sales for the date range minus 1 year (i.e. 1st Nov 12 to 30th Nov 12).

             

            I have attached a spread sheet which might better show the layout I am trying to achieve.

             

            Thanks for your assistance so far.

            • 3. Re: Show sales for selected date range and selected date range minus 1 year
              john.finn

              Hi Tracy,

               

              thanks for your help. I managed to get this working... thanks again.

              • 4. Re: Show sales for selected date range and selected date range minus 1 year
                Lisa Tango

                Hi Tracy and John,

                 

                I am trying to do the same thing, with sales for a date range compared year over year.  Specifically, I am trying to do sales month to date compared year over year, but am not able to get it to work.  Can you please provide more detailed instructions or attach the workbook?

                 

                Thank you very much.

                • 5. Re: Show sales for selected date range and selected date range minus 1 year
                  john.finn

                  Hi Lisa,

                   

                  I can't post up the workbook where I resolved the YOY sales comparison but I will detail the steps I used to achieve same. Hopefully you can work it out from these...

                   

                  1) Created Parameter START DATE (Date Type = Date, Display Format = Automatic, Allowable values = All )

                   

                  2) Created Parameter END DATE (properties as above)

                   

                  3) Created Calculated Field SALES TP (this period) with formula:

                  IF [Start Date] <= [DocDate] and [DocDate] <= [END DATE] THEN [SALESIncVat]

                    

                  Change [DocDate] to be your date field and [SalesIncVat] to be your sales field

                   

                  4) Created Calculated Field SALES LP (last period) with formula:

                  IF [Start Date] <= DATEADD('year', 1,[DocDate]) AND

                  DATEADD('year',1,[DocDate]) <= [End Date]

                  then [Sales Inc Vat] END

                   

                  Again change fields to be your fields.

                   

                  If you are interested, I then created a Sales Diff (value) and a Sales Diff (%) also... (hope this doesn't look like I am showing off but as this is my first deployment I was chuffed to get these working!!)

                   

                  5) Created a Calculated Field SALES +/- with formula:

                  ZN(SUM([Sales TP])) - LOOKUP(ZN(SUM([Sales LP])), 0)

                   

                  6) Created a Calculated Field SALES +/- % with formula:

                  (ZN(SUM([Sales TP])) - LOOKUP(ZN(SUM([Sales LP])), 0)) / ZN(SUM([Sales LP]))

                   

                  A screenshot of a worksheet using these values below shows layout, filters etc.

                   

                  Hope this works for you...

                   

                  John.

                   

                  Sales Analysis YOY.png

                  • 6. Re: Show sales for selected date range and selected date range minus 1 year
                    Lisa Tango

                    Hi John,

                     

                    Thank you so much – it worked!!  Only problem is I need to do this for many measures that are in rows:

                     

                     

                     

                     

                     

                    I can create the necessary calculated fields for each measure, but am not sure how to get the layout to look like above.

                     

                    Great to have help from overseas! (I had to look up ‘chuffed’).  ☺

                     

                    Thank you,

                    Lisa