9 Replies Latest reply on Sep 24, 2013 3:41 AM by Prashant Sharma

    latest week end date in a calculated field

    Philip George


      Hi,

       

      I have a dataset like the attched item.

       

      week end date               sales

       

      I want to create  a new calculated filed which will always store the latest weekend date.(example:15/09/2013).can anyone please help me to create a calculated filed for this.

       

      I want to create a dashboard which will always display the last 10 weeks sales.I know that relative date filter can be used for this.But here I cannot use that.

       

      So I am planning to get a calculated filed which will store the latest week end date.later I will use this field for displaying the previous 10 weeks.

       

      regards,

      philip George

        • 1. Re: latest week end date in a calculated field
          brad.earle.1

          If you are using Excel as your datasource, the easiest way is to just add a column to the data -- call this MaxWeekenddate.  The Excel formula is simply Max(A:A).  That will create a field that is the same in all your rows, thereby enabling a row level evaluation of whether the weekend date in column A is within your 10 week horizon.

           

          But, if you want to make it even easier, use Excel's weeknum(Max(A:A)) - weeknum(A) to figure out how many weeks the date is in the past.  Then just use that field for filtering.

           

          Let me know if you don't have the ability to modify the Excel source file, as there are other ways to get the same results.

          1 of 1 people found this helpful
          • 2. Re: latest week end date in a calculated field
            Philip George

            Many thanks  for the reply.

             

            Actually I use sql server as the datasource.So I think creating a calculated field inside tableau itself to find out the latest week end date is the proper solution.

             

            Please let me know if you know any ways to do that.

             

            Regards,

            Philip George

            • 3. Re: latest week end date in a calculated field
              Prashant Sharma

              Hi,

              I think you can use the following formula that will show only latest weekend date(For me i took 'Friday'). I used this formula in another calculation that provided me the sales of last 10 weeks. Following is the code for latest weekend date:-

              "Latest Weekend Date" (Field Name)

               

              if datename('weekday',max([Order Date])) = 'Friday' then max([Order Date])

              else if datename('weekday',max([Order Date])) = 'Monday' then date(max([Order Date]) - 3)

              else if datename('weekday',max([Order Date])) = 'Tuesday' then date(max([Order Date]) - 4)

              else if datename('weekday',max([Order Date])) = 'Wednesday' then date(max([Order Date]) - 5)

              else if datename('weekday',max([Order Date])) = 'Thursday' then date(max([Order Date]) - 6)

              else if datename('weekday',max([Order Date])) = 'Saturday' then date(max([Order Date]) - 1)

              else if datename('weekday',max([Order Date])) = 'Sunday' then date(max([Order Date]) - 2)

              end end end end end end end

               

              Following is the formula to calculate the last 10 weeks sales:-

              if [Latest Weekend Date]>=date([Latest Weekend Date]-70) and [Latest Weekend Date]<date(([Latest Weekend Date]+1)) then sum([Sales]) end

               

              Concept is here. You can change the formula according to your requirement.

               

              Warm Regards,

              Prashant Sharma - India | LinkedIn

              2 of 2 people found this helpful
              • 4. Re: latest week end date in a calculated field
                Shawn Wallwork

                Here is first day of this week: DATETRUNC('week', TODAY() ))

                Here is first day of 10 weeks ago: DATEADD('week', -10, DATETRUNC('week', TODAY() ))

                 

                --Shawn

                2 of 2 people found this helpful
                • 5. Re: latest week end date in a calculated field
                  Prashant Sharma

                  Hi Philip,

                  Answer provided by Shawn is more accurate but according to your requirement you just have to modify it a bit.

                  It provides 'Sunday' date(weekstart date) always(as datetrunc() always returns the starting of the datepart mentioned as argument).

                  So, if you want Friday date then you just have to use DATEADD('day',-2,DATETRUNC('week', TODAY() )))

                  Hope it helps:)

                  Warm Regards,

                  Prashant Sharma - India | LinkedIn

                  1 of 1 people found this helpful
                  • 6. Re: latest week end date in a calculated field
                    brad.earle.1

                    SQL server solution is to use custom sql -- again, I understand your issue is to get the latest weekend date from a column of dates.  That date would then be used to figure out whether a particular row was within your 10 week data window for displaying results.  My approach for this type of activity is to place it into the datalayer.  Yes, it can be done in Tableau with a table-calc, but then it is an aggregated field which means that it can only be used in further calculations that use aggregated fields.  Can this be worked through?  Sure, but my preferred way is to just get the max date as its own field from the datasource.

                     

                    So, if you are unfamiliar with SQL, you can take the Tableau connection of table(s) and then select the radio button for Custom SQL.  From there, I'd add just before the 'From' statement a line like this:

                    ,(Select max(Weekenddate) from SQLTable) as MaxWeekenddate

                     

                    (Based on your file, it looks like the column you have already has the weekenddate shown for each row of data -- the above approach will always reflect the latest weekend dates in the data, so that if for some reason the data isn't refreshed, the Tableau calcs  to show 10 weeks will still be internally consistent with the data.)

                     

                    Because this sql is an inline subquery, using a direct connection may result in longer than desired response times.  I'm figuring you know to make this an extract in order to regain performance.

                    1 of 1 people found this helpful
                    • 7. Re: latest week end date in a calculated field
                      Justin Larson

                      have you tried

                       

                      total(max([Datefield]))

                       

                      total () tells the formula to look at the whole table, not just the context of a single mark. It will be effected by a filter, however. So it would report back the latest date that is not omitted because of a filter.

                      • 8. Re: latest week end date in a calculated field
                        Philip George

                        Hi,

                         

                        I created one  calculated field to find out the latest weekend date with little modification to what prashant has given.this gave me a new datedimension field with latest week date.This worked perfectly for my requirement.

                         

                        if datename('weekday',today()) = 'Saturday' then today()

                        else if datename('weekday',today()) = 'Monday' then (today() - 2)

                        else if datename('weekday',today()) = 'Tuesday' then (today() - 3)

                        else if datename('weekday',today()) = 'Wednesday' then (today() - 4)

                        else if datename('weekday',today()) = 'Thursday' then (today() - 5)

                        else if datename('weekday',today()) = 'Friday' then (today() - 6)

                        else if datename('weekday',today()) = 'Sunday' then (today() - 7)

                        end end end end end end end

                        ----------------------

                         

                        I have one other table which has a column fiscal week.This column is a string and Column values are as follows.i dont have the actual date dimension behind this fiscalweek column.

                         

                        2013Q1WK01

                        2013Q1WK02

                        2013Q1WK03

                        ---

                        --

                        --

                        2014Q1Wk01

                        2014Q1Wk02

                        --

                        --

                        --

                         

                        I want to findout the latest financial week from this string column and I want to store it into  a new column which should be dimension.I can use max(fiscalweek).but this field will go under the measure values.later If I try to use this field to compare with actual fiscal week in an if statement I will get an error saying cannot mix aggregates and non aggregates.

                         

                        tableau filters will not get updated with the most recent data after publishing.(I dont want to use relative filters here)to overcome this issue I am creating a new field which will always have the latest week date and later i will compare with actual fiscal week and have the report with only recent data.but if the user need a particular date view then I will create a parameter.

                         

                        Can anyone suggest any other idea if there is a better one to overcome this filter update issue?

                         

                        Regards,

                        Philip George

                        • 9. Re: latest week end date in a calculated field
                          Prashant Sharma

                          Hi Philip,

                          For calculating maximum week from "2013Q1WK01" this format, you can use following calculation:-

                          Max(left(fiscalweek,4)+right(fiscalweek,2))

                          Let me know if this will not going to help you. Now both are the Aggregated fields & now you will be able to compare both.

                          Warm Regards,

                          Prashant Sharma - India | LinkedIn