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

# Sales Lookup for Non-Standard Periods

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

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:

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

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

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

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