Dynamic weekly average per category

Question

I have a dataset as follows:

DATE       | AMOUNT | CATEGORY
20.12.2015 | 100.00 | Drinks
22.12.2015 | 50.00  | Food
20.12.2015 | 70.00  | Transport
07.12.2015 | 50.00  | Transport
...

There are several records with amounts spent per week and day.

I would like to have a bar chart with the categories on the left and the length of the bars indicating the weekly average, ie. what is spent on average per week during a filtered time frame

If I user the normal AVG([AMOUNT]) it calculates the daily average, rather than the weekly one.

I found this question: Tableau - weekly average from daily data

However one of the answers is not dynamically, the other lists averages for consecutive weeks, rather than per category and I can't think of a way to apply the same technique for mmy problem.

sample output would be useful to test. – vhadalgi Dec 24 '15 at 05:33 — vhadalgi, Dec 24 '15 at 05:33

score 2 · Answer 1 · answered Dec 23 '15 at 16:04

2

Add a new dimension, which is for the weeks
You can then create a variable which calculates the average amount for a specific week as follows: {FIXED [Date (Week numbers)], [Category]: avg([Amount]) }
Then when you want to average you can average the above formula AVG({FIXED [Date (Week numbers)], [Category]: avg([Amount]) })

answered Dec 23 '15 at 16:04

Mark Andersen

868
6
8

You don't need to use LOD calls for this. You can, but it isn't necessary. The main step is to make sure you treat date as a dimensional the week level of aggregation. – Alex Blakemore Dec 25 '15 at 03:43
That kind of worked for me. I just had to change `avg([Amount])` to `sum([Amount])` since, I want to calculate the average of the accumulated spendings per week. The problem now is that Tableau calculates the AVG based on the amount of weeks I spent s.th. in. If I spent 40$ in week 1, 20$ in week 2 and 30$ in week 4, then it calculates `(40+20+30)/3 = 30` while I would like to have `(40+20+30)/4 = 20.25`. Any idea how to solve that? – Alexander Jan 05 '16 at 09:38

score 0 · Answer 2 · answered Dec 24 '15 at 04:52

0

First make sure the data type for the field named DATE is type date instead of string. If not, change the data type from the right mouse menu or worst case use the date parse function in a calculated field.

Second, after you place the DATE field onto a shelf, set the date level of granularity to Week. Again ], use the right mouse context menu. Choose from the second batch of choices to truncate dates to the week level. The first batch of options on that menu are date parts, not dates. You may want to then change the field to discrete depending on your intended view.

answered Dec 24 '15 at 04:52

Alex Blakemore

11,301
2
26
49

I am not sure if you missunderstood my description or if I missunderstood your solution. However for the moment that doesn't seem to be what I want. I don't want a date dimension on one of the shelves, I want Category on the rows and AVG(Spendings) as the length of the bars. The problem is to have the AVG calculated as the average per week over all weeks in a defined timeframe. i.e. Timeframe 1.12.-31.12. should give me Total Spendings / 5 weeks – Alexander Jan 05 '16 at 09:33

score 0 · Accepted Answer · answered Jan 05 '16 at 09:59

Based on Mark Andersen's solution I found the following:

create a calculated field WeekNumber:

DATETRUNC('week', [Date])
create a calculated field WeekTotal:

{FIXED [WeekNumber], [Main Category]: SUM([Amount Person]) }
create a calculated field WeekDiff:

DATEDIFF('week',#2015-08-01#,TODAY())
create a calculated field WeekAvg:

[WeekTotal] / [WeekDiff] Use WeekAvg as the meassure for the bars and it's done.

A few remarks for that: Mark's solution went int he right direction. I had to replace avg([Amount]) with sum([Amount]) since I want to have the total per week and average it afterwards. However it didn't exactly calculate what I wanted since Tableau only calculates averages based on the weeks that have a spending. If I have

40$ in week 1  
20$ in week 2 
30$ in week 4

then it calculates (40+20+30)/3 = 30 while I would like to have (40+20+30)/4 = 20.25 In my use case my solution works because I have a fixed time frame until TODAY(), however it would be conviniant if that would be calculated automatically if I use a filter between two arbitrary dates.

Dynamic weekly average per category

3 Answers3