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

# Compare last day result with 4 weeks average

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:

 Date Sales 1/1/2017 100 1/2/2017 120 1/3/2017 133 1/4/2017 150 1/5/2017 160 1/6/2017 200 1/7/2017 100 1/8/2017 125 1/9/2017 132 1/10/2017 36 1/11/2017 35 1/12/2017 74 1/13/2017 100 1/14/2017 123 1/15/2017 150 1/16/2017 170 1/17/2017 180

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

 WD Avg by WD Monday 141 Tuesday 116 Wednesday 93 Thursday 117 Friday 150 Saturday 112 Sunday 125

So for example, 01/17/2017 is Tuesday

 Date Sales Avg by WD Difference 1/17/2017 180 116 64

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

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

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

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

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

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

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

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:

 Date Sales 1/1/2017 100 1/2/2017 90 1/3/2017 80 1/4/2017 56 1/5/2017 60 1/6/2017 30 ... ... 1/24/2017 56 1/25/2017 60 1/26/2017 30 1/27/2017 20 1/28/2017 10

And last month daily sales:

 Date Sales 12/1/2017 200 12/2/2017 190 12/3/2017 180 12/4/2017 156 12/5/2017 160 12/6/2017 130 ... ... 12/24/2017 156 12/25/2017 160 12/26/2017 130 12/27/2017 120 12/28/2017 110

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