6 Replies Latest reply on Dec 27, 2017 11:46 PM by Skaiste Papuskaite

    Compare last day result with 4 weeks average

    Skaiste Papuskaite

      Hello,

      I am making daily sales report. In order to understand if this day was "good" or "bad" I want to compare last day results with an average by week day.

      If last day is Monday, I want to show difference between the last day result and an average of previous Mondays.

       

      My data looks similar to this:

        

      DateSales
      1/1/2017100
      1/2/2017120
      1/3/2017133
      1/4/2017150
      1/5/2017160
      1/6/2017200
      1/7/2017100
      1/8/2017125
      1/9/2017132
      1/10/201736
      1/11/201735
      1/12/201774
      1/13/2017100
      1/14/2017123
      1/15/2017150
      1/16/2017170
      1/17/2017180

       

      And the averages that I want to compare daily results with are:

       

      WDAvg by WD
      Monday141
      Tuesday116
      Wednesday93
      Thursday117
      Friday150
      Saturday112
      Sunday125

       

      So for example, 01/17/2017 is Tuesday

        

      DateSalesAvg by WDDifference
      1/17/201718011664

       

      And I need to be able to change the Date.

       

      Maybe someone has an idea how to do that? I've tried few options, but it didn't work.... :/

      Any help would be very very useful!

        • 1. Re: Compare last day result with 4 weeks average
          Matthew Theis

          Hello Skaiste Papuskaite  I think you can use the date filter as well as a calculated field (AVG by WD) that will provide the day provided the date (as selected by the filter).  You can then pull in the calculated field into your columns.  This should return the day you are looking for DATENAME('weekday',[Date]). 

          I have to give credit to Ben Young in his response to this thread: https://community.tableau.com/thread/147716

          1 of 1 people found this helpful
          • 2. Re: Compare last day result with 4 weeks average
            Norbert Maijoor

            Hi Skaiste,

             

            Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread

             

             

            1. D0. date (weekdays)

             

             

            2. D1. Date selected weekday: if ([D0. date (Weekdays)])=[P1. Select weekday] then ([date]) end

             

            3. D2. Last selected weekday: {fixed: max([D1. Date selected weekday])}

             

            4. P1. Select weekday

             

             

            5. M1. Average of selected weekday: avg(if ([D0. date (Weekdays)])=[P1. Select weekday] then  ([value]) END)

             

            6. M2. Last selected weekday: if [D2. Last selected weekday]=[date] then [value] END

             

            7. M3. Delta: sum([M2. Last selected weekday])-[M1. Average of selected weekday]

             

            8. Drag required objects to the indicated locations and show parameter control.

             

             

            Regards,

            Norbert

            1 of 1 people found this helpful
            • 3. Re: Compare last day result with 4 weeks average
              Skaiste Papuskaite

              Thanks Matthew. Problem is not how to get week days, but to see actual day result and difference between it and average of that weekday from previous few weeks. :/

              1 of 1 people found this helpful
              • 4. Re: Compare last day result with 4 weeks average
                Skaiste Papuskaite

                Thanks a lot Norbert. But the problem  I have is other way around I have a result of a certain day (as in example - 01/17/2017) and this result must be compared with a weekday average.

                I want to give my colleagues possibility to change this date, but every day have to be compared with a relevant weekday average.

                 

                Can you help me with that?

                1 of 1 people found this helpful
                • 5. Re: Compare last day result with 4 weeks average
                  Matthew Theis

                  Skaiste Papuskaite  Check out what I've done here...

                   

                  skaiste1.png

                  with [date] as a slider filter, you can select any day.  the LOD will give you the Average for the day corresponding with that date.  Then a simple calculated field [Difference] which is (SUM([value])-[LOD])  will give you the difference between the two.  You'll have to play with formatting, but you'll get the numbers you're looking for here.

                  Did this help or answer the question?

                  Best,

                  Matthew

                  1 of 1 people found this helpful
                  • 6. Re: Compare last day result with 4 weeks average
                    Skaiste Papuskaite

                    Thanks, Matthew. Sadly is not working right..... If I leave just one day (last day before today) avg is no longer counted......

                    Is there a possibility to link two different results by weekday?

                    I have two different tables:

                    4 weeks results:

                      

                    DateSales
                    1/1/2017100
                    1/2/201790
                    1/3/201780
                    1/4/201756
                    1/5/201760
                    1/6/201730
                    ......
                    1/24/201756
                    1/25/201760
                    1/26/201730
                    1/27/201720
                    1/28/201710

                     

                    And last month daily sales:

                      

                    DateSales
                    12/1/2017200
                    12/2/2017190
                    12/3/2017180
                    12/4/2017156
                    12/5/2017160
                    12/6/2017130
                    ......
                    12/24/2017156
                    12/25/2017160
                    12/26/2017130
                    12/27/2017120
                    12/28/2017110

                     

                    I want to make a dashboard where you can choose a day of the last month and see not only daily sales results, but also difference from those 4 weeks based on weekday.

                    For example:

                    12/26/2017, Tuesday

                    Daily Sales = 130

                    Difference = Daily Sales - avg(4 weeks Tuesday Sales).

                     

                    12/28/2017, Thursday

                    Daily Sales = 110

                    Difference = Daily Sales - avg(4 weeks Thursday Sales).

                     

                    Do you have any ideas how can I do this?

                     

                    1 of 1 people found this helpful