7 Replies Latest reply on Dec 1, 2016 4:40 AM by Norbert Maijoor

# Understanding aggregation issues

Hi

I am having a problem with aggregation that makes me think I don't understand how Tableau's functions are working so I would really value an explanation!

I have a data set with datetime, location id and volume fields - one row per location per hour

Sdate                                  Cosit             Volume

13/10/2006 09:00:00    000036000005    10

13/10/2006 09:00:00    000036000006    1

13/10/2006 10:00:00    000036000006    4

I wanted to aggregate the volumes into specific periods of the day.  So I have created calculated fields such as 'total volume 4pm-7pm':

IF DATEPART('hour', [Sdate]) >=16 AND  DATEPART('hour', [Sdate]) <19 THEN [Volume] END

I also want to express those volumes as percentages so I've created calculated fields such as 'percent 4pm-7pm'

([total volume 4pm-7pm] / [Volume]) *100

Now when I look at the outputs in data table the volumes have worked but the percentages haven't:

If I change the 'percent' calculations from SUM to ATTR then the value each is 100.

So I think I have misunderstood the logic of how the calculations work.

Many thanks

Dave

• ###### 1. Re: Understanding aggregation issues

Hello Sharpington,

you can right click the field and use quick table calculation to find the percentage of volume.

Hope this helps you.

Regards,

Sudhakar Reddy

• ###### 2. Re: Understanding aggregation issues

Hi D,

Find my approach based on "Quick table calculation" as reference below and stored in attached workbook version 9.3

• ###### 3. Re: Understanding aggregation issues

Hi Sudhakar

yes, I had tried that but as you can see form the screenshot below, when I use the quick table calculation 'Percent of Total' they are both shown as 100%, whereas from the figures shown in my first post the percentage is 2961497 / 12603448 = 23%.

Dave

• ###### 4. Re: Understanding aggregation issues

Hello Sharpington,

Since your trying to calculate percent of total for that specific measure it will be showing 100 % only.

Regards,

Sudhakar Reddy

• ###### 5. Re: Understanding aggregation issues

Hey Sharpington,

WINDOW_SUM(SUM[total volume 4pm - 7pm]))/WINDOW_SUM(([volume]))*100 .

Regards,

Sudhakar Reddy

• ###### 6. Re: Understanding aggregation issues

Hi Norbert

yes, got it now thank you! My mistake was to create separate fields rather than grouping one field, so of course the percentage calculations were only referencing the filtered field so were always 100%.

Incidentally, as I had many rows I first created a calculated field of hours only.

• ###### 7. Re: Understanding aggregation issues

Hi D,

You are welcome. Thanks for the "awards"! Much appreciated:)