12 Replies Latest reply on May 7, 2018 7:11 AM by Julia.rose

    Create company yearweek x-axis

    Julia.rose

      Hi

       

      I want to draw a line chart of two metrics. My axis should be company yearweek (eg:2016wk1 will be starting from 10th Jan 2016).It is different from calendar year week(2016wk1 will be starting from 1st Jan 2016).Main problem is to get the company yearweek in date format on x-axis

       

      Currently i have company yearweek  as a string from .csv file

       

      I am not sure how to do this. I ran into many problem in all methods suggested on the forums.

       

      After I achieve this, i need to add reference line to x-axis which should automatically change based on the store i chose.

        • 1. Re: Create company yearweek x-axis
          swaroop.gantela

          I'm not sure if this got there,

          but maybe this can give an idea.

           

          The idea is to have 2016/01/10 as the basedate

          and then add on weeks depending on the value of Year Week.

           

          I first created a basedate:

          MAKEDATE(2016,01,10)

           

          I then converted the Year Week to a string

          and calculated the week adjustment as:

          (INT(LEFT([Year Week (copy)],4))-YEAR([BaseDate]))*52

          +

          (INT(RIGHT([Year Week (copy)],2))-1)

           

          Then added the adjustment to the basedate:

          DATEADD('week',[AdjustWeek],[BaseDate])

           

          Please see the workbook attached in the Forum thread.

          1 of 1 people found this helpful
          • 2. Re: Create company yearweek x-axis
            Julia.rose

            Hi Swaroop

             

            My tableau version is 10.2.2

            I tried changing the xml version but it didnt open.I used the below thread for reference.

            Open workbook with newer version

            Is it possible to downgrade your workbook and resend please.

            • 3. Re: Create company yearweek x-axis
              swaroop.gantela

              Apologies.

              Please try the attached.

              1 of 1 people found this helpful
              • 4. Re: Create company yearweek x-axis
                Julia.rose

                Thanks Swaroop!

                This also gave the problem I got of producing wk53.

                Company week starts from sunday and 2016wk1 should be 10th Jan 2016 and every year has only 52 weeks. how can we adjust it so we dont see week 53 in any year.

                After week52, week1 starts for next year

                • 5. Re: Create company yearweek x-axis
                  swaroop.gantela

                  If the goal is to convert the Company Year Week into a date,  then can use:

                  DATEADD('week',

                  INT(RIGHT(STR([Year Week]),2))-1,

                  MAKEDATE(INT(LEFT(STR([Year Week]),4)),1,1))

                   

                  Then can plot the exact date as continuous and format the axis

                  like the selection 2001 W1.

                   

                  If you need the actual dates, can use the conversion method as described above

                  and put that actual date in the tooltip.

                   

                  Reference line can be converted with:

                  { FIXED [Store]:MIN(IF [Year Week]=[Reference Line Week] THEN [Plot Date] END)}

                   

                  Please see workbook attached.

                   

                  269063date.png

                  • 6. Re: Create company yearweek x-axis
                    Julia.rose

                    Hi Swaroop!

                     

                    This is brilliant work! And so close to what we want.

                     

                    Can you provide a step-by-step process since the dates on x-axis are not aligning with cursor date on metric line when i replicated the calculations you made.(Attached is my attempt to replicate your view)

                    where should i add this formula?

                    "{ FIXED [Store]:MIN(IF [Year Week]=[Reference Line Week] THEN [Plot Date] END)}"

                     

                     

                    Also,Can we fix the below in your view somehow?

                     

                    When i chose weeks 2017W44 to 2018W2, we want the focus on that period itself and not see the reference line which was on 2016W52. This is because the customer would want to zoom into whichever periods and see all trends properly

                    1.JPG

                     

                    Can we align the reference line with the week?

                    1.JPG

                    • 7. Re: Create company yearweek x-axis
                      swaroop.gantela

                      Hmm. All the date-shifting is skewing the tick marks.

                      Will likely need some different kinds of date calcs.

                      Will have to ponder.

                       

                      Certainly open to other ideas from the Community.

                       

                      In the meantime, going back to square one, would

                      a simpler (albeit not as clean) approach work?

                      If you don't really need the exact date, how about sticking

                      with plotting a discrete string version of Year Week

                      and just marking along the line where the reference point is?

                      This would solve both the alignment and filter issues.

                       

                      Please see workbook attached.

                       

                      269063date2.png

                      • 8. Re: Create company yearweek x-axis
                        Julia.rose

                        Hi Swaroop

                         

                        Sigh! I had to do a similar work-around. Used the yearweek as a string

                        itself and used a barplot and used dual-axis to overlap them. Then, recoded

                        the reference line.

                         

                        step1: created Group_Date:

                        STR(ATTR(IF ="Store 1" Then 201701

                        ELSEIF =" Store  2" Then 201805

                        ELSEIF =" Store  3" Then 201809

                        ELSEIF ="Group 4" Then 201601

                        Else Null

                        END))

                         

                        step 2: Then ref_line_bar:

                        CASE ATTR()

                        WHEN

                        THEN WINDOW_MAX(SUM())*1.025

                        ELSE null

                        END

                         

                        Step3: Used ref_line_bar to plot.

                        Year Week in columns. Measure values and ref_line_bar in Rows. Dual Axis.

                         

                        This gave me what I needed.I cannot make a dotted line here for the

                        reference line and many other functionalities of reference line.

                        Its still useful if I can make the yearweek of my company into date format

                        or continous axis, so the 'add reference line' option gets enabled.

                        Please let me know if you think of something for that.

                         

                         

                         

                         

                         

                         

                         

                         

                        On Thu, May 3, 2018 at 7:03 PM, swaroop.gantela <

                        • 9. Re: Create company yearweek x-axis
                          swaroop.gantela

                          I'm not giving up.

                          Sorry for the slow iterations.

                           

                          Going back a couple of versions:

                          the idea is that one gets the exact date (like 2016/1/10) using the conversion

                          method described before (splitting the string, adding a BaseDate determined by the year).

                          This exact date gets used in the tooltips.

                           

                          But for Plotting, we are going to plot a shifted date back to 2016/1/1, but we will format

                          the label as YYYY\ \Www to make it look like your company week of 2016 W1.

                           

                          I think the alignment issues arose because the true 2016 W1 actually starts 2015/12/27.

                          So I've added in another shift depending on year so that the beginning of the year/01/01

                          is week one (by making the start of the week be Friday for 2016, Sunday for 2017, etc).

                          Please check the sheet called "table" in the attached.

                          It doesn't look to me like there are any week53s.

                           

                          I think this second shift helps the alignment. Please see the sheet "alignment".

                           

                          Somehow, I think this also helps the graphs with reference lines only showing if they

                          are in the range.  Please note that I had to make a separate reference line for each store.

                          How many stores are in your true data set?

                           

                          The date filter is set to change by Company Week ([PlotDate (shift day)] which seemed to me

                          better than using actual dates.

                           

                          Very likely that issues remain, and that there are unforeseen effects of all the shifting.

                          But please take a look to see if this is millimeters closer to the goal.

                          • 10. Re: Create company yearweek x-axis
                            Julia.rose

                            Hi Swaroop

                             

                            The idea sounds great! The lines have to align to be presentable to our

                            customers.

                            The dual axis chart i made looks like this attached.

                            I can use this for now. For each store i select the reference line moves as

                            per assigned date in the code. The number of stores for this project is

                            below 20.

                            I see a week 53 because i believe tableau assumes 365 days to be an year.

                            when we tell that sunday to sunday(7 days) make a week, then we get 365/7=

                            53 weeks.

                             

                            I gave an example that my company week starts from sunday (for

                            confidentiality purposes).

                             

                             

                             

                             

                            On Fri, May 4, 2018 at 7:29 PM, swaroop.gantela <