-
1. Re: Sales Lookup for Non-Standard Periods
swaroop.gantela Nov 14, 2018 8:49 PM (in response to Bren Perkins)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:
-
287646prev.twbx 78.0 KB
-
-
2. Re: Sales Lookup for Non-Standard Periods
Bren Perkins Nov 15, 2018 7:16 AM (in response to swaroop.gantela)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 Nov 15, 2018 7:35 AM (in response to Bren Perkins)1 of 1 people found this helpfulBren,
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.
-
4. Re: Sales Lookup for Non-Standard Periods
Bren Perkins Nov 15, 2018 8:10 AM (in response to swaroop.gantela)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