How to sum the average over a month

Question

I am trying to sum data relating to database table sizes and can't seem to do what I need to do. I capture database name, table name and table size every day. Example data would look like this:

Database Name  Table Name  Date        Size
Database 1     Table 1     01/01/2016  0.01
Database 1     Table 1     02/01/2016  0.02
Database 1     Table 1     03/01/2016  0.02
Database 1     Table 2     01/01/2016  0.41
Database 1     Table 2     02/01/2016  0.42
Database 1     Table 2     03/01/2016  0.43
Database 2     Table 1     01/01/2016  0.01
Database 2     Table 1     01/01/2016  0.01
Database 2     Table 1     01/01/2016  0.01

I want to produce a simple line chart showing database size over time. If I add database name in row, Date in column and Size in value, I get a line chart showing sum of Size per database. This gives the correct database size when viewed at daily level, as the correct database size value at that granularity is simply the sum of size of all tables within each database. With the above data, for example, Database 1 on 01/01/2016 should be 0.42.

However, when I look at month level, incorrect amounts are shown. Tableau is summing all values for each database in each month. So in the above example for month January 2016 and Database 1 I get a value of 1.31. I need to get Tableau to get the average value for each table in each month, then sum all of those average table sizes per database to get the average total database size for that month.

I'm, sure it's possible but I can't find a way to do it.

Here you are talking multiple aggregation. That is achievable in 2 steps. 1st avg() you size , and then use window_sum for next level of aggregation. Be sure to know more about window calcs. The order of calculation is important there depending upon the layout of your sheet. — minatverma
Thanks for the suggestion. I tried window_sum but couldn't get it to do what I was looking for. I'm new to Tableau. Any chance you could work that up into an answer with a rough step by step please? — Aphillippe
Let me know what data is needed for output, to validate my calculation.input I will take what you posted . — minatverma
The output I am looking for is a line chart with the following data points: Database 1 Jan 2016 0.44 Database 2 Jan 2016 0.01 where the value is the sum of (average for each table size in that month) — Aphillippe

minatverma minatverma · Accepted Answer · 2016-02-26T16:10:40

create a calculated field avg_table_size as :

{ FIXED [Table Name],[Database Name] : AVG([Size])}

This field will get you the avg. size per table for the data frame you have . please note you will have to modify to include other dimensions you add to table except date

Now coming to the line chart , since you have only one month data line chart will require more months . That will be easy I guess after you have more data . Use SUM(avg_table_size) for line chart you wish to create .

Let me know if this worked for you .

How to sum the average over a month

1 Answers