7 Replies Latest reply on Mar 14, 2013 5:03 PM by arthi.keat

    Help with Week Table Calculations

    arthi.keat
      Hi,
      I was getting confused on how we can get some thing like the below table.
      We can use the sample Superstore Sales data as an example.
       

      Yesterday

      03/10/2013

      % WeekDay

      (ie:Sunday of this Week  vs. Sunday Last Week for example 03/10/2013 vs 03/03/2013

      % of Last Week¹s Avg.

      (Sunday vs. Last week Fri-Fri average, 03/10/2013 with average of 03/01/2013 to 03/08/2013)

      SalesAcual Number% Higher/Lower than Avg% Higher/Lower than Avg
      ProfitAcual Number% Higher/Lower than Avg% Higher/Lower than Avg
      QuantityAcual Number% Higher/Lower than Avg% Higher/Lower than Avg
      I wanted it to be a automated daily update with yesterday's data. Can you please help me create some thing like this.
      Thanks for your time.
      Regards,
      Arthi.
        • 1. Re: Help with Week Table Calculations
          Dimitri.B

          Arthi, do you need to include full days for both Fridays in the last column?

          • 2. Re: Help with Week Table Calculations
            arthi.keat

            Thank you looking through the question and replying.

             

            I apologize, for not mentioning correctly. We need to include only one Friday for the whole week average. Does that make sense?

             

            Please let me know how it can be done..

            • 3. Re: Help with Week Table Calculations
              Dimitri.B

              So, what would be the start and end of the week, i.e. Sunday to Saturday, and would that be different from the 7-day period specified in the last column, i.e. Saturday to Friday?

              • 4. Re: Help with Week Table Calculations
                arthi.keat

                Yes, it would be a Saturday to Friday week. And compare the previous day with previous Saturday to Firday Week.

                 

                Please let me know if you have any questions

                • 5. Re: Help with Week Table Calculations
                  arthi.keat

                  For better understanding, I tried attaching my workbook but itseems like it is too large to be uploaded. I tried zipping too but no luck. If you have ideas on how the above tabel calculations can be done, it would help me a lot complete it.

                   

                  1. The week period to be considered would be full Saturday to full Friday
                  2. The weeks to be considered would be the current and previous two weeks.(if there is data)
                  3. I wanted it to be a automated daily update with yesterday's data.
                  4. I wanted the table to look similar to the one below 
                  Yesterday (for eg: Sunday)% W/W Last Wk¹s (for eg: Sunday vs. last Sunday)

                  %Last Week's Avg

                  (for eg Sun v/s Last Week Sat to Fri Avg)

                  Sales

                  Profit

                  Thank you for looking in to this..

                   

                  Regards,

                  Arthi.

                  • 6. Re: Help with Week Table Calculations
                    Dimitri.B

                    Attached is one possible solution, for one measure (Sales). You can repeat it for all other measures, but you'll have to use a dashboard to show them together if you want it to look like your mock-up.

                    The main complication is non-standard week, as Tableau is not flexible with week definition - Tableau's week is Sun to Mon.

                    The general steps to solution are:

                    1. Create a date field to shift dates to desired start of week
                    2. Based on the above, create custom week numbers field
                    3. Create custom calculations to get sales for Sundays, yesterday, etc.
                    4. Use table calculations to calculate difference from previous period
                    5. Set up the sheet with the above measures
                    6. Set aliases for measure names to display the desired measure labels
                    7. Create a special field (Show current week) to allow filtering the view to show only current week, but keep the data for previous week in the background, and configure the filter.
                    • 7. Re: Help with Week Table Calculations
                      arthi.keat

                      Thank you so much for pulling this together. Really appreciate your time and help.

                       

                      I was able to create a small data set for what I am trying to do. I was able to do some things but would require your help in combining the rest. Can you please help take a look and provide your comments.

                       

                      My questions are in the worksheet, in the captions.

                       

                      Please let me know how we can solve this.. thank you so much for your help.

                       

                      Best Regards,

                      Arthi.