9 Replies Latest reply on Sep 21, 2016 7:09 AM by Samuel Gee

# Mixing agg and non-agg in IF statement

I have 1000 titles. I want to list the top 20 by sales, and aggregate the others into a single row that says "880 others".

I have a set in the Title dimension, of the Top 20 by sales. It's called "Top 20".

The following doesn't work, because the countd of title is an aggregate but nothing else is.

If [Top 20] then [Title] else STR(COUNTD([Title]))+" Others"  END

How do I achieve my "880 others" row?

• ###### 1. Re: Mixing agg and non-agg in IF statement

Well, since you can't mix aggregates and non-aggregates, you need to either make everything aggregate or make everything non-aggregate. For the first option, you could replace [Title] and [Top 20] by ATTR([Title]) and ATTR([Top 20]) respectively. Alternatively, you could create a FIXED LOD expression that pre-calculates your COUNTD([Title]).

• ###### 2. Re: Mixing agg and non-agg in IF statement

Samuel,

I would assume you wanted something like this.

If so create a field called Index with Index() as the value inside it and then create another field lets call it Top Vs Bottom and define a If condition as below

if [Index] < 10 THEN 'Top 10'

ELSE 'Rest All'

END

Good Luck.

• ###### 3. Re: Mixing agg and non-agg in IF statement

Thanks Stephen.

I tried that, but consistently get "1 Other", as in the attached workbook, with the following:

If ATTR([Top 10]) then ATTR([Title]) else STR(COUNT([Title]))+" Others" END

Can you suggest a workaround?

Sam

(I'd like this field to count the titles in the view, as it's going to be re-used in a number of different circumstances, so I'm not sure a FIXED is suitable).

• ###### 4. Re: Mixing agg and non-agg in IF statement

Thanks Anoop,

I actually want the "Rest all" to say "200 others" or "400 others" depending on the count of titles not in the Top x.

Does that make sense?

• ###### 5. Re: Mixing agg and non-agg in IF statement

Samuel,

I am sorry but I missed it. Are you looking for a changing field name ? i.e if you have 300 obs then 'top 100' and then '200 others' or if you have 800 obs in total then 'top 100' and '700 others'

• ###### 6. Re: Mixing agg and non-agg in IF statement

Precisely that

• ###### 7. Re: Mixing agg and non-agg in IF statement

check this article out, it helped for me. I've also posted in this article some tweaks that I used to make mine make more sense.

• ###### 8. Re: Mixing agg and non-agg in IF statement

Create one more field to get the total count of Observations. I am using Super Store and the field I am using is Product Name. So a new field called Calculation3 which is as below (considering you wanted Top 20 and Rest. Change the value of 20 accordingly)

STR('Other ') +

STR({EXCLUDE [Product Name] : COUNTD([Product Name]) } - 20)

then change the formula for the field 'Top vs Bottom' as below.

if [Index] < 10 THEN 'Top 10'

ELSE ATTR([Calculation3])

END

This will change the Title as and when the number of Observations changes.

Good Luck.

1 of 1 people found this helpful
• ###### 9. Re: Mixing agg and non-agg in IF statement

Thank you - I was actually just posting my own solution when your email came through, haha!

If ([Top 20]) then [Title] else STR({ FIXED :COUNTD([Title])}-20)+" others" END

Apparently wrapping an aggregate like COUNTD inside a LOD means it's fine working with non-aggregates in an IF statement. I don't know why, but its' super useful to know!

It requires that I add a number of filters to the context, which normally I'd try to avoid, but as this sheet won't be changing that frequently at all it's not an issue here.