Data aggregation based on time in Tableau

Question

I am trying to create aggregated data based on a time dimension as follows: Example of data:
DateTime,FlowID,Volume
t0,1,100
t1,2,200
t2,3,300
t2,4,400
t2,5,100
t3,4,100

When I visualize sum(Volume) over the DateTime dimension, Tableau correctly shows the total volume for time t2 as the sum of all the records at time t2 (300+400+100).
I am also trying to get a view that shows a table based on hours of the day as follows:
                                      DateTime
                           0      1      2      ...      23
-----------------------------------------------------
Sum Volume      x      y      z      ...       t
Max Volume       a      b      c      ...      d

I want Max Volume to be the sort of "local maximum " during the hour (in this case the sum of all volume records across all the FlowID instances that occurred within the same DateTime t2).

I have looked into the running_max function as follows:
running_max(sum([Volume]),-2,0), but that only gives me the last two records at time t2. I don't know a-priori how many records end up being at exactly the same time, so I can't use -2 or -3 deterministically.

Any ideas?

You state you want Max Volume to be "in this case the SUM of" ... Do you mean "the MAX of" ...? If so, then wouldn't just Max(Volume) be enough, i.e. changing the aggregation function to Max? — Alex Blakemore
I want the max volume row to be the max of the sum of all the volume values that exist at the same DateTime value. In this case, if times t1 and t2 are within the same hour mark (hour 1 under the DateTime table), the sum of all FlowID for t1 is 200 (only one record), the sum of all FlowID for t2 is 800 (300+400+100), so the max volume that should appear in the table for column 1 should be 800 (max of volume sum at times t1 and t2, which is max(200,800)) — user3780013

Alex Blakemore Alex Blakemore · Accepted Answer · 2014-06-29T05:53:03

Try window_max(sum(Volume)) which is a table calc. So you'll need to also specify which dimension to use for addressing, i.e., compute using. That is as important as the formula and might take a little experimentation to get right.

Based on your description, I think you want to compute using FlowId.

Data aggregation based on time in Tableau

2 Answers