CODEWITHSUNDEEP
tableau-api
Aphillippe
Aphillippe

Reputation: 655

How to sum the average over a month

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.

Upvotes: 1

Views: 5855

Answers (1)

minatverma
minatverma

Reputation: 1099

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 .

Upvotes: 1

Related Questions