5 Replies Latest reply on Nov 28, 2016 8:28 AM by Timothy Vermeiren

    Max/Penultimate/Groupings using set periods

    Jo Fox

      Hi all

       

      I'm looking to create a viz that shows sales figures, but instead of dates, the data uses set periods. I want the user to be able to select period to date, last period, last 13 periods, etc, however I can't find the right way to do a period offset parameter. I can't link the dates with periods as the dates change regularly (so date offset won't work), I've tried blindly using rank, index, max etc without any success at all! An example of the data is found below.

       

      I'm a newcomer to Tableau so any help you can give me would be really appreciated,

       

      Thank you!

       

      Jo

       

       

       

      YearPeriodYr.PdSales
      2015/1612015.01214
      2015/1622015.025328
      2015/1632015.033295
      2015/1642015.04395
      2015/1652015.0539195
      2015/1662015.063817
      2015/1672015.07294
      2015/1682015.089592
      2015/1692015.092950
      2015/16102015.103049
      2015/16112015.1118573
      2015/16122015.125869
      2015/16132015.1330492
      2016/1712016.0157273
      2016/1722016.0259304
      2016/1732016.031874
      2016/1742016.045937
      2016/1752016.053532
      2016/1762016.067537
      2016/1772016.077245
      2016/1782016.084567
      2016/1792016.09

      254

        • 1. Re: Max/Penultimate/Groupings using set periods
          Timothy Vermeiren

          Hi Jo,

           

          See attached workbook for an example of how this could be solved, with additional interactivity. This solution makes use of a parameter, to allow the user to select a period to compare to (the "anchor"). Based on this selection, you can use either of the calculated fields prefixed "Filter " to filter a sheet the way you want.

           

          Have a look and play around with the examples in the workbook, and see if this is something that you could use. In case you don't want the user to select the period, but want to determine the current period automatically: that would also be possible with an additional calculated field that determines this period, instead of using the parameter.

           

          Let us know if this is clear!

           

          Cheers,

           

          Timothy

          • 2. Re: Max/Penultimate/Groupings using set periods
            Jo Fox

            Hi Timothy

             

            Thank you for the reply, however, I'm using version 9.0 and so can't download the workbook I'm sorry!

            • 3. Re: Max/Penultimate/Groupings using set periods
              Timothy Vermeiren

              Hi Jo,

               

              Sorry, wasn't aware of that! You'll find a version 9 workbook attached to this post.

               

              Cheers,

               

              Timothy

              1 of 1 people found this helpful
              • 4. Re: Max/Penultimate/Groupings using set periods
                Jo Fox

                That's perfect! Thank you : )

                 

                How easy is it to add a calculation showing current year/period? I was playing around a bit using your calculations, but am running in to difficulties finding the last Yr/Pd using a dimension instead of measure.

                 

                Thank you again

                 

                Jo

                • 5. Re: Max/Penultimate/Groupings using set periods
                  Timothy Vermeiren

                  Hi Jo,

                   

                  I'm not completely sure I understand your request, but I'm assuming you are trying to show the most recent period+year in the data. Attached, you'll find another workbook containing two more sheets. These show the current year's latest period, and the current + previous year's latest periods, respectively.

                   

                  The trick is to use Level of Detail calculations that are FIXED, but without a dimension specified, which means they will return the aggregated value for the whole data set. For example:

                   

                  { MAX([Period]) }
                  

                   

                  This will return the maximum value of Period for the whole data set. The keyword FIXED is omitted in this case, but this is indeed treated as a FIXED expression.

                   

                  I'd suggest you have a look at some of the calculations used in the workbook and let me know if anything is still unclear.

                   

                  Cheers,

                   

                  Timothy