7 Replies Latest reply on Mar 20, 2013 3:12 PM by Ashley Howard

# Aggregations in Case Statement

Hi,

I am trying to create a calculated value with the case statement shown below:

CASE [store_group]

WHEN 'H' THEN AVG(IF [store_group] = 'H' THEN [sales] ELSE 0  END )

WHEN 'F' THEN  AVG(IF [store_group] = 'F' THEN [sales] ELSE 0 END )

WHEN 'S' THEN  AVG(IF [store_group] = 'S' THEN [sales] ELSE 0 END )

WHEN 'M' THEN  AVG(IF [store_group] = 'M' THEN [sales] ELSE 0 END )

END

So the above should check every row, and wherever the [store_group] equals 'H' it returns the average value for all the "sales" values of [store_group] = H.

However i am getting an error "Cannot mix aggregate and non-aggregate comparisons or results in case", i dont see any mixing of aggregations in the above.

Could any one shed any light please?

Gareth

• ###### 1. Re: Aggregations in Case Statement

Try replacing CASE [store_group] with Case Attr([store_group]).

• ###### 2. Re: Aggregations in Case Statement

Hi Ashley,

Yep that removed the error, but what exactly does it do?

Gareth

• ###### 3. Re: Aggregations in Case Statement

There is a mix - think of it this way:

For every row, check [store group] for a letter and then average the sales.

But as the check is occurring on every row, there is no average that can be created.

I would guess you don't need the case at all - why not have store group as a pill and avg([sales]) as another?

I wonder if you are trying to create a % of average per store group? If so we need to use table calculations.

• ###### 4. Re: Aggregations in Case Statement

The average should be computed over all the rows in the table (that apply), and not on the current row that is being processed.

Therefore basically there are really only 4 average values that need to be computed one for "H", "S", "F" and "M" then according to what the current row type is the appropriate average value gets assigned.

Not sure if that makes it clear.

• ###### 5. Re: Aggregations in Case Statement

Got it - you need table calcs - there are some videos in the on-demand section that help explain them - the difficulty comes in the partitioning (how we decide what to average across).

The attached shows the calculation you need: window_avg(attr([sales]))

There are two copies of this calculation, one set with the "compute using" to table down - this gives the average for all of the order IDs in the view. The second is set to "Order ID". This means that category is now the field across which the average is calculated.

Without knowing how you want to use the average sales it's difficult to help you set this up properly.

1 of 1 people found this helpful
• ###### 6. Re: Aggregations in Case Statement

Excellent, il have a look and see how it goes.

Thanks a lot

• ###### 7. Re: Aggregations in Case Statement

Alex is right that a Table Calculation is a more efficient way of accomplishing what you want.

Attributes allow you to aggregate dimension regardless of the level of detail in your viz.   You can read more about attributes at the links below.

Good example:

http://onlinehelp.tableausoftware.com/current/pro/online/en-us/calculations_aggregation_aggregatingdata.html

Technical details:

http://community.tableau.com/message/175152