-
1. Re: Mixing agg and non-agg in IF statement
Stephen Rizzo Sep 20, 2016 12:49 PM (in response to Samuel Gee)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
anoop.mohandas.0 Sep 20, 2016 1:41 PM (in response to Samuel Gee) -
3. Re: Mixing agg and non-agg in IF statement
Samuel Gee Sep 21, 2016 4:29 AM (in response to Stephen Rizzo)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).
-
Demo.twbx 16.2 KB
-
-
4. Re: Mixing agg and non-agg in IF statement
Samuel Gee Sep 21, 2016 4:28 AM (in response to anoop.mohandas.0)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
anoop.mohandas.0 Sep 21, 2016 6:33 AM (in response to Samuel Gee)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
Samuel Gee Sep 21, 2016 6:36 AM (in response to anoop.mohandas.0)Precisely that
-
7. Re: Mixing agg and non-agg in IF statement
Kyle Boyce Sep 21, 2016 6:51 AM (in response to Samuel Gee)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
anoop.mohandas.0 Sep 21, 2016 7:04 AM (in response to Samuel Gee)1 of 1 people found this helpfulCreate 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.
-
9. Re: Mixing agg and non-agg in IF statement
Samuel Gee Sep 21, 2016 7:09 AM (in response to anoop.mohandas.0)Thank you - I was actually just posting my own solution when your email came through, haha!
I had
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.
Thanks for your help!
Sam