Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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