4 Replies Latest reply on May 15, 2012 11:43 AM by Andrew Watson

    Compare time range with a same time range of 1 year ago

      Hello,

      I can see the SALES of a state (eg. Arizona) in a filtered time range (eg. Nov-2011 to Feb-2012) It is easy and OK.

      But, I want to compare the SALES figures of Arizona with the same time range of 1 year ago. ( Nov-2010 to Feb-2011)

       

      Notice that, time range is dynamic so it can be changed by filter manually and SALES last have to be calculated accordingly

       

      Helps are appreciated.

        • 1. Re: Compare time range with a same time range of 1 year ago
          Fionnuala Gibney

          Hi Bill,

           

          This is very similar to the problem described here:

          http://community.tableau.com/thread/117545

           

          And using the technique  Create a calculated field using the LOOKUP() table calculation function to use for the filter. This filter will only be applied after evaulating the moving average, so has the effect of removing the entries from the display without affecting the calculation.

           

          So I created a new Calculated Date field, called [Order Date for Filter], and used that for the Order Date filtering.

          LOOKUP(MIN([Order Date]),0)

           

          And used a normal Percentage Difference across the table for the Sales.

           

          Attached example.

          Fionnuala

          • 2. Re: Compare time range with a same time range of 1 year ago

            Hi Fionnuala,

             

            I may be misunderstood so I changed the twbx file as attached.

            What I want is to see the last of what I filtered for now.

            In the attached file, as you move the Order Date scroll, orange and blue circles have to show the sales of the filtered time period and last year sales figures of same date range. But it does not.

             

            is the problem more clear now?

             

            thanks in advance.

            • 3. Re: Compare time range with a same time range of 1 year ago
              Fionnuala Gibney

              Afraid I've been trying to work on this and am not getting much headway, sorry!

              • 4. Re: Compare time range with a same time range of 1 year ago
                Andrew Watson

                Hi Bilal

                 

                I am unable to open your attached workbook as my employer is still using Tableau 6.1. However I have done something similar to what it sounds like you're trying to achieve.

                 

                My solution was to use parameters to select the date - i.e. have a start date and an end date parameter. From that you can build calculated fields for this year and last year which should enable you to do the desired comparison.

                 

                2 calculated fields will be required, one for last year and one for the selected period. If we call the selected period calc ThisYear the field would be something like:

                 

                IF Date >= StartDateParameter AND Date < EndDateParameter THEN Sales END.

                 

                The calc for last year would be very similar but using dateadd to minus a year:

                 

                IF Date >= DATEADD('year',-1,StartDateParameter) AND Date < DATEADD('year',-1,EndDateParameter) THEN Sales END

                 

                Without seeing you vis it's hard to advise much more. You might need to create some sort of calculated field to act as a filter, it all depends how you're trying to display the data. I was using this data in a bullet chart to didn't display any time period on the X axis so didn't have to do any date based filtering so these calc fields worked really well for me. Hopefully this will help a bit.

                 

                Andrew