1 Reply Latest reply: Jan 21, 2011 3:01 PM by Hector Muñoz

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

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!

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

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