Show a count of the number of sales that fall into a range within a pie chart

[#1]

I have a table of "deals". Here's an example set of data:

ID Type Amount

#1 sale 1100

#2 sale 3000

#1 fee 100

So the total value of deal #1 is 1200.

I need to show a pie chart which shows the count of sales that have a values between 0-500, 500-1000, and >1000.

I've been trying to come up with a formula for the dimension field. So far I have:

IF( Amount <1000, IF( Amount < 500, '0-500', '>500', '>1000)) .... then my expression is Count(ID)

This works OK but isn't really correct. I think it's counting sale #1 as being > 1000 and < 500 and sale #2 as being > 1000.

I need it to SUM the same IDs to get a total value so the correct result should be just 2 results for above 1000.

Not sure how to add SUMs to the IF statement. Thanks!

[hector · Accepted solution]

Hi, i've used aggr() in a calculated dimension, like this

=if(aggr(Sum(Amount),ID)<=500,'0-500',
 if(aggr(Sum(Amount),ID)>500 and aggr(Sum(Amount),ID) < 1000,
 '500 - 1000',
 '> 1000'
 )
)

and the expression

Count (DISTINCT ID)

It works if i understood ok xD, and i added 1 record under 1000 for test purposes, check the file and tell us if this is what you want

Rgds