
1. Re: Splitting Column values into multiple column
Simon Runc Jun 30, 2016 9:16 AM (in response to J Jack)1 of 1 people found this helpfulhi Jack,
So unlike an excel pivot table, where to do a YoY calculation (for example) you need a 2015 Sales and 2016 sales year, Tableau prefers the data in one Column...however I'll give you both solutions so you can choose.
To create a year dimension
[Year]
RIGHT([X],4)
To Create the week Dimension
[Week]
INT(LEFT([X],2)) //I've wrapped it in an INT so it sorts properly.
You can then just put the [Year] in the column shelf (making it discrete, blue pill) and then drag you Y column into the pane and voila...You can then use the 'quick table calc' to get your YoY.
If however you so want 2 separate fields for TY and LY
[2015 Sales]
IIF([Year]=2015,[Y],0)
[2016 Sales]
IIF([Year] = 2016, [Y],0)
Hope that does the trick, and makes sense. If not post back and I can work you up an example

2. Re: Splitting Column values into multiple column
J Jack Jun 30, 2016 10:24 AM (in response to Simon Runc)Hello Simon,
Thanks a Ton, the second logic works for me, with that I was able to calculate the percentage change.
Next question I have is: for 2015 I have 52 weeks of sale and for 2016 I have only 20 weeks of sale. So, whenever I want to see the percentage, I am applying filter on both the years, eg if I want to see first 20 weeks sales percentage for 2016, I am selecting 1st 20 weeks from 2016 and 1st 20 weeks from 2015 on filter, to get the exact percentage. If not applying filters it is giving complete 2015 52 weeks vs 2016 20 weeks. Is there a way to restrict, if I publish the dashboard, it has to take the update automatic, as the week gets added. is it possible ?
Thanks
J

3. Re: Splitting Column values into multiple column
Simon Runc Jun 30, 2016 10:56 AM (in response to J Jack)hi Jack,
So yes...that's why I also created the Week Column...you can just use this (make it continuous and a dimension, green pill) and add to your filter shelf and you'll get a nice slider where you can choose weeks 120 (or whatever range). The week number 1, say, applies to both years, so you won't have to individually select 40 different year/week combinations.
Yes we can make this dynamic, although this will bring up some more advanced features. You still seem to be using Tableau like Excel (but an Excel that can hold more than 1 Million rows!)...Tableau works more like a database so wants dimensions and measures in single columns
I've worked you up a quick example of what I mean...One sheet with the manual slider on weeks, and the other creating the calculation dynamically (even dynamically picking up the last year and week of the data so requires zero maintenance, even when you change years!). I don't want to confuse you, as this takes a bit of understanding, so will only go into it briefly...you may find the following Quora answer on calculations in Tableau useful Answer  Quora
As you'll read there it's all about the vizLoD. In this example I've used a FIXED LoD. This is a special calculation that runs, regardless of the level of detail in the Viz (it's a bit like using SUMIFS in Excel)
So
[Max Year of Data]
{MAX([Year])}
brings back the MAX year in the data, and this is applied to every row
The next bit is a little bit complicated...
[Max Week of Max Year]
{MAX(IIF([Year]=[Max Year of Data],[Week],NULL))}
so the IIF part populates a new pseudofield with the week, if it is the MAX year (NULL if not). So all the 2016 rows, have the week against them (all the 2015 are NULL). We then nest in a further LoD to get a MAX of this pseudofield. So in this case it returns the number 20.
We can then create our filter
[YtD Filter]
[Week] <=[Max Week of Max Year]
so it only returns the rows (for either year) where the Week <= 20....this is why it's important to have the data going Column Down, as it makes this kind of thing much easier. btw this data model, is why Tableau can run calculation in seconds over 100s millions of rows, and Excel struggles with a few 100 thousand!!
I don't expect this will make perfect sense, but as the quora answer advises, watch the calculation videos and then it should start to make sense.

YtD Example.twbx 40.7 KB


4. Re: Splitting Column values into multiple column
Andrew Connolly Jun 30, 2016 11:04 AM (in response to J Jack)Try thisyou can either modify the first calculation, or make another one
IIF([YEAR] = 2015 AND [WEEK] <= DATEPART('week',today()),[Y],0)
I should clarify and say that mine is far simpler than Simon's because instead of actually finding the max date in your data, is just operates off of the current date. If your data is ever stale (even just by a day or two) Simon's method will still return the correct value. Mine may not!!

5. Re: Splitting Column values into multiple column
J Jack Jul 1, 2016 9:00 AM (in response to Simon Runc)Thanks SImon,
it helps a lot, appreciate your response. Am new to this tool and learning lot of new things.
Thanks again
J

6. Re: Splitting Column values into multiple column
J Jack Jul 1, 2016 9:02 AM (in response to Andrew Connolly)Thanks Andrew !!
Appreciate it !! :))
J

7. Re: Splitting Column values into multiple column
J Jack Jul 3, 2016 5:48 AM (in response to Simon Runc)Simon,
One more req, when it comes to filter, as i have 52 week for 2015 and 20 week for 2016, i can group them into two radio button, one for 2015 and other for 2016. Is it way I can add two more radio button below it, one for YTD(2016) and 4th for last 4 weeks of (2016), now i have 20 weeks for 2016 so last 4 weeks should be (201617,18,19,20). Thanks
J

8. Re: Splitting Column values into multiple column
Simon Runc Jul 3, 2016 5:59 AM (in response to J Jack)Hi Jack,
So one way to do this would be to use a parameter. In the attached I've created the following parameter
btw you don't have to use Integer values, and then Display as...this just my preference for several reasons.
Once we have this we can expose it, and set it be radio buttons in format.
I can the create my filter as follows
[Selected Time Period Filter]
CASE [Show Time Slice For]
WHEN 1 THEN 1
WHEN 2 THEN IIF([Week] <=[Max Week of Max Year],1,0)
WHEN 3 THEN IIF([Week] <=[Max Week of Max Year] AND [Week] > ([Max Week of Max Year]4) ,1,0)
END
I then bring this onto the filter shelf, and set it 1...and now the user can control if they see everything, YtD, last 4 weeks...or any other time slice you might want. I'v e left week in so you can see what it's doing, but you can remove this (as the formulas are Row Level logic statements...so run 'off canvas')

YtD Example.twbx 60.6 KB


9. Re: Splitting Column values into multiple column
J Jack Nov 15, 2016 3:43 PM (in response to Simon Runc)Hello Simon,
hope you are doing good. Need your help in building the logic which you have suggested couple of months back.
To the earlier logic I have built this calculations.
as I have 52 weeks of Data for 2015
and 41 weeks of Data for 2016
FISCAL FYTD starts from 201601 ends 201652
Calendar year CYTD starts from 201549 end 201648
To below I am able to get case 1 to 8 for FYTD and case 11 to 13 which are working fine. I am facing Issues on 9 and 10 for CYTD
I have issues in the below case 9 and 10 (detail below)
Case 9:where I have to show CYTD which starts from 201549 and ends at 201648 or Max Week Of Max year 201641 ( which ever comes first) and this should stop at 206148, should not add data if week 201649 is added
case 10: where I have to show CYQ1 which start from 201549 and ends 201609
CASE [Time Selection]
WHEN 1 THEN IIF( [YEAR]= [Max Year] and [Week] <=[Max Week Of Max Year],1,0) ## working fine give me FYTD from 201601 till 201641 (upto last week)
WHEN 2 THEN IIF([YEAR] = [Max Year] AND [Week] <=[Max Week Of Max Year] AND [Week] > ([Max Week Of Max Year]4) ,1,0) ## working fine, gives me Last 4 week of 2016
WHEN 3 THEN IIF(([YEAR] = [Max Year] and [Week] >= 01 and [Week] <= 13) ,1,0) ## working fine gives me first quarter of 2016
WHEN 4 THEN IIF(([YEAR] = [Max Year] and [Week] >= 14 and [Week] <= 26) ,1,0) ## working fine FY Q2
WHEN 5 THEN IIF(([YEAR] = [Max Year] and [Week] >= 27 and [Week] <= 39) ,1,0) ## working fine FY Q3
WHEN 6 THEN IIF(([YEAR] = [Max Year] and [Week] >= 40 and [Week] <= 52) ,1,0) ## working fine FY Q4
WHEN 7 THEN IIF([YEAR] = [Max Year] AND [Week]=[Max Week Of Max Year],1,0) ## working fine, give me last week of 2016 which is week 41 of this Year 2016
WHEN 8 THEN IIF([YEAR]=[Min Year],1,0) ## working fine give me last year complete (2015)
**WHEN 9 THEN IIF([YEAR]=[Min Year] and [Week] >= 49 and [Week] <=[Max Week Of Max Year],1,0)** not working** when I select this parameter it should select from 201549 and 201641(max week of year) is called CYTD**WHEN 10 THEN IIF(([YEAR] = [Min Year] and [Week] >= 49 ) and [Week] >= 09 ,1,0) ** not working** When I select this parameter it should give me from 201549 till 201609 (CY Q1)
WHEN 11 THEN IIF(([YEAR] = [Max Year] and [Week] >= 10 and [Week] <= 22) ,1,0) ## working fine CY Q2
WHEN 12 THEN IIF(([YEAR] = [Max Year] and [Week] >= 23 and [Week] <= 35) ,1,0) ## working fine CY Q3
WHEN 13 THEN IIF(([YEAR] =[Max Year] and [Week] >= 36 and [Week] <= 48) ,1,0) ## working fine CY Q4
END
Need your assistance
Thanks in advance
J

10. Re: Splitting Column values into multiple column
J Jack Nov 15, 2016 4:21 PM (in response to J Jack)Got it
Fyi,
WHEN 9 THEN IIF([YEAR]=[Min Year] and [Week] >= 49 or [YEAR] = [Max Year] and [Week] <=48,1,0)
WHEN 10 THEN IIF(([YEAR] = [Min Year] and [Week] >= 49 or [YEAR] = [Max Year] and [Week] <=09) ,1,0)
Thanks
J