13 Replies Latest reply on Apr 12, 2016 8:37 AM by Corey Turner

    Determining this week

    Edward Yeldham

      We have a field in Tableau which is our Financial week which comes in "hard coded" from our source data.  I would like to create a calculated field to determine "This week" so I could also then determine next week and last week as this +/- 1 for comparative analysis on a dashboard.

       

      I have a screen shot of the worksheet below and we are in week 40 today (Wednesday so only sales up to and including Tuesday).  How can I determine that the current week is 40.  If I had a current week calculated field I could then show comparatives as I could put my current week calculated field in my filter shelf.  may seem an odd question but any help?


      Ed

       

       

        • 1. Re: Determining this week
          Corey Turner

          Ed,

           

          You can write a "Current Week Number" calculated field as:

           

          DATEPART('week', NOW())

           

          If your current week isn't actually the current week of "today" and you want the maximum date/week of your data set you could try something like this:

           

          Table Calc way: IIF(ATTR([Date]) = WINDOW_MAX(MAX([Date])), 'Current Date', 'Prior Date')

          LOD way: IFF([Date] = {FIXED : MAX([Date])}, 'Current Date', 'Prior Date')

           

          You may need to change the "Compute Using" of your table calc depending on your level of detail in your viz. Hope it helps.

          1 of 1 people found this helpful
          • 2. Re: Determining this week
            Edward Yeldham

            Corey,

             

            Thanks for your response, those calcs are interesting/useful but don't really do what I want them to do.  I wanted something that would return the actual week number of the current week as the answer.

             

            As per my attachment we are currently in week 40 so I'd like to get the result to tell me this so that I could then do some further logic on this.


            Any thoughts?

             

             


            Ed

            • 3. Re: Determining this week
              Corey Turner

              Ed,

               

              Sorry that didn't get what you were looking for, but now I'm a little confused as to exactly what you need. You said you want something that returns the actual week number of the current week....  DATEPART('week', NOW()) does this. For April 7, 2016, we get week 15.

               

              I mentioned if the current week if dependent on your data (the hard coded values from your database) you would then have to do things a little differently with a table calc or an LOD.

               

              Let me try again... you can try this table calc (#1) or LOD (#2):

               

              1. WINDOW_MAX(MAX([Transaction Financial Week]))

              2. {FIXED : MAX([Transaction Financial Week])}

               

              These will return the maximum week number in your data set (being 40 from your screeenshot). If this still doesn't solve it, maybe upload an example twbx and describe what you are looking for in more detail. Hope this get it done for you!

               

              Corey

              • 4. Re: Determining this week
                Edward Yeldham

                Corey

                 

                Thanks for your patience it is appreciated but I must be doing something wrong as not getting it to work.

                 

                Attached is a revised screen shot.  I am working with the Financial Transaction Week field which as you can see underlined in red is not a date field.  Transaction date is.  I won't bore/confuse you with why we did this.  My current report shows the current weeks sales and prior 2 on a relative filter.


                Ideally what I want to do is have the current weeks sales, next weeks sales and last weeks sales but shown by current year and prior years.  To do this i need to know what the current week is to then create 3 calculated fields one for current week, then one for next week and one for last week.  These 3 calculated fields could then be plotted against the years.

                 

                As you can see the financial week and native tableau week for the current year are the same so I could use the transaction date to return me the figure 40 for the current week (in theory) and then use this in my calculated field.

                 

                Using the Datepart ('Week',Now()) gave me an odd result as shown in my second screen shot?

                 

                The Window_Max also was odd as it gave me the answer 9 not 40 so very confused altogether now.

                 

                I'd try and send some data but not sue how to do that plus is a big big data set.

                Ed

                • 5. Re: Determining this week
                  Corey Turner

                  Ed,

                   

                  Thanks for more details. This is tricky without playing with a workbook, but here are some thoughts that should get you a lot closer....

                   

                  Your second screen shot you are summing this field... That will sum 15 over all the rows of your data to equal 147,432,810. If you just change this to a dimension, make it discrete (blue) you should get week 15.

                   

                  Your third screen shot is looking for the maximum week as a string or text field. If you change the data type of this field you should be set. Right click the Transaction Financial Week and "Change Data Type" to Number (whole).  OR you can convert it in the calculated field if you still want the field to remain as text. WINDOW_MAX(MAX(INT([Transaction Financial Week]))) This should now return 40.

                   

                  If you are able to post a twbx, I could help you get the views you described comparing the next/prev week for multiple years.

                  • 6. Re: Determining this week
                    Edward Yeldham

                    Corey,

                     

                    So some progress as I got the answer of 40 but the data doesn't behave how I expected it to with it.  I have managed to attach an extract of my data.


                    If you look at "This Week Sales vs Last 2 Weeks" the sales for week 40 are 28,879 which is the current week.


                    Sheet 21 and 22 are the different versions of the calculation.


                    When I have determined the week my plan was to create a calculated field to filter on.

                     

                    If it knew the current week was 40 then I could get it to tell me last week was 39 and next week 41.  I could then generate a table as below

                     

                                                  2015/2016                         2015/2014          2014/2013

                    Last Week               xxxxx                                   xxxxx               xxxxxx    

                    Current Week          28,879                                   xxxxx               xxxxxx         

                    Next Week               (blank as no data yet)          xxxxxx               xxxxxxxx

                     

                    Any thoughts?


                    Ed

                    • 7. Re: Determining this week
                      Corey Turner

                      Edward,

                       

                      Check out the attached twbx, sheet 24.

                       

                      I was able to get with a nested LOD calculation. You could do this with table calcs too, but I'd recommend LOD in this case.

                       

                      IF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])} THEN

                          'Current Week'

                      ELSEIF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])}-1 THEN

                          'Last Week'

                      ELSEIF INT([Transaction Financial Week]) = {FIXED: MAX([Max Transaction Financial Week])}+1 THEN

                          'Next Week'

                      END

                       

                      Cheers!

                      1 of 1 people found this helpful
                      • 8. Re: Determining this week
                        Edward Yeldham

                        Thanks will look at that.  I'm on 9.2.4 you have done in a newer version?


                        Ed

                        • 9. Re: Determining this week
                          Corey Turner

                          Yes, I did it in 9.3 sorry about that. Are you able to update your version of Desktop?

                          1 of 1 people found this helpful
                          • 10. Re: Determining this week
                            Edward Yeldham

                            Yes but need to upgrade our server first so not a 5 min job.  When done all that I'll have a look at the workbook thanks.


                            Ed

                            1 of 1 people found this helpful
                            • 11. Re: Determining this week
                              Corey Turner

                              You can have multiple versions of Tableau installed on a client computer. Shouldn't be a need to install server first.

                              • 12. Re: Determining this week
                                Edward Yeldham

                                Corey

                                 

                                Now installed and seen what you did, really really helpful and much appreciated thanks for your help.  Now to understand the fixed formula..........


                                Ed

                                • 13. Re: Determining this week
                                  Corey Turner

                                  No problem! Glad it is helpful.

                                   

                                  Regarding the fixed formula... That is an LOD (level of detail) expression. And it basically returns an aggregate number at a different level of detail than what you've built in the view. Tableau actually generates a subquery to the original data source to the level of granularity you specify. They can get complex just like table calculations, but they are really useful. See the overview page here.