4 Replies Latest reply on Nov 15, 2018 8:10 AM by Bren Perkins

    Sales Lookup for Non-Standard Periods

    Bren Perkins

      Howdy all, I'm hoping for some help.  My company uses a non-standard period format which is a combo of FY and Period (2017001 for 2017 Period 1) and each FY has thirteen periods.  I created a parameter for the user to enter an FY Period and what I want to find is the difference between the parameter FY Period and the Prior Period or the difference between the parameter FY Period and the same Period LY. 

       

      So, a user keying 2017001 would expect to see the difference between 2017001 and 2016013 (which is the prior period); as well as the difference between 2017001 and 2016001 (LY Period).  Date calcs don't seem like they'd work due to nature of the FY Period concatenation.

       

      I'm using version 10.5.2 and I'm (attempting) to attach a sample workbook.  I'm reasonably new to the nuances of Tableau so if you can dumb down your answers I'd really appreciate it.  Thanks, and all the best!

       

      Bren 

        • 1. Re: Sales Lookup for Non-Standard Periods
          swaroop.gantela

          Bren,

           

          I didn't look closely at your calculated fields, but I think you had the gist and were pretty much there.

           

          So first I sought to just get the string of the previous period by performing the string manipulation,

          then checked to see if that row's period equals the string manipulation, then return the string of the period.

          Mostly involves turning the string into an integer, doing the subtraction, then turning it back into string

          and reassembling the yearPeriod structure.

           

          IF [Ordered (FY Per)]= (

              IF RIGHT([FY Per Selection],2)="01"      // handle 01 separately

                  THEN STR(INT(LEFT([FY Per Selection],4))-1)+"013"  // subtract a year and add to 13

              ELSEIF RIGHT([FY Per Selection],2)<"10"    // handle those that need two zero padding

                  THEN LEFT([FY Per Selection],4)+"00"+STR(INT(RIGHT([FY Per Selection],1))-1)

              ELSE LEFT([FY Per Selection],4)+"0"+STR(INT(RIGHT([FY Per Selection],2))-1)

              END )

          THEN [Ordered (FY Per)]

          END

           

          So the previous period's sales would become:

          IF [Ordered (FY Per)]=[Previous Period]

          THEN [Sales]

          END

           

          The last year same period would be:

          IF [Ordered (FY Per)]=

              (STR(INT(LEFT([FY Per Selection],4))-1)+RIGHT([FY Per Selection],3))

          THEN [Ordered (FY Per)]

          END

           

           

          This method requires four calculated fields for each measure (sales, profit, etc.).

          You can possibly reduce this to just four total if you can pivot your measures:

          Pivot Data from Columns to Rows - Tableau

           

          (can even be further condensed down to just two by condensing,

          for example, the prev period calc with the prev period sales calc).

           

          Please see workbook v10.5.6 (I think you can still open it with 10.5.2?) attached in the Forum Thread:

          https://community.tableau.com/thread/287646

          1 of 1 people found this helpful
          • 2. Re: Sales Lookup for Non-Standard Periods
            Bren Perkins

            Hi Swaroop, thank you so much for the in-depth answer; I really appreciate it.  I wasn't able to open the workbook you attached but I keyed the calcs as you laid them out; they worked perfectly with the sole exception of the difference between Period 10 and the prior period...this produced no result. 

             

            Everything else is amazing; I understand the idea of what you did but the specifics of how it works far elude me...I'll have to dissect and really think about it.  I don't know if you have any ideas about getting Period 10 to work but otherwise it's a top-notch solution!

             

            Bren

            • 3. Re: Sales Lookup for Non-Standard Periods
              swaroop.gantela

              Bren,

               

              Apologies about that, I forgot to separately consider ten.

              Stringwise, it goes from one leading zero "010" to two leading zeros "009":

               

              IF [Ordered (FY Per)]= (

                  IF RIGHT([FY Per Selection],2)="01"

                      THEN STR(INT(LEFT([FY Per Selection],4))-1)+"013"

                  ELSEIF RIGHT([FY Per Selection],2)<"10"

                      THEN LEFT([FY Per Selection],4)+"00"+STR(INT(RIGHT([FY Per Selection],1))-1)

                  ELSEIF RIGHT([FY Per Selection],2)="10"

                      THEN LEFT([FY Per Selection],4)+"009"

                  ELSE LEFT([FY Per Selection],4)+"0"+STR(INT(RIGHT([FY Per Selection],2))-1)

                  END )

              THEN [Ordered (FY Per)]

              END

               

              Sorry that it's not really a systematic approach, but just an attempt to cover all the bases.

               

              Also sorry that I wasn't able to downgrade to a lower version, I think on account of the hyper extract.

              1 of 1 people found this helpful
              • 4. Re: Sales Lookup for Non-Standard Periods
                Bren Perkins

                Swaroop, you are a freaking genius!  That worked like a champ; thank you so much for all of your time writing out both the formula and explanations in such detail.  Thanks again and I really appreciate it!

                 

                Bren