10 Replies Latest reply on Nov 15, 2016 4:21 PM by J Jack

    Splitting Column values into multiple column

    J Jack

      Hello All,

       

      Need your expertise on the below request:

       

      I have to show the percentage change in sale from This year to Last year, but my data for two years is not in separate columns like sales for year 2015 and sales for year 2016. Instead it has only two columns X and Y, X for Years and Y for sales.

       

      X has values like, 201501, 201502, (till last week of 2015) 201552 and for 2016 it has values like 201601, 201602, 201603 till 201652, and corresponding 01,02,03 are weeks for the year, eg if it says 201501 then it is first week of 2015, and 201652 means last week of year 2016.

       

      So it is like this

         X                 Y

      201501          10

      201502           20

      201503          30

      .

      .

      201601          10

      201602           20

       

      So want something like below out of that

          X1                Y1               X2                Y2

      201501            10              201601          10

      201502             20             201602           20

       

      I can do it on Excel separating the values, but every time, I can't do this, cause as it has other data to play around. trying to create a calculated field, but not coming up with the idea. Also, the columns split functionality in Tableau is not good option in this scenario.

       

      J

        • 1. Re: Splitting Column values into multiple column
          Simon Runc

          hi 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

          1 of 1 people found this helpful
          • 2. Re: Splitting Column values into multiple column
            J Jack

            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

              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 1-20 (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 data-base 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 pseudo-field 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 pseudo-field. 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.

              • 4. Re: Splitting Column values into multiple column
                Andrew Connolly

                Try this--you 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

                  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

                    Thanks Andrew !!

                     

                    Appreciate it !! :-))

                     

                    J

                    • 7. Re: Splitting Column values into multiple column
                      J Jack

                      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

                        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')

                        • 9. Re: Splitting Column values into multiple column
                          J Jack

                          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

                            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