Simple count not working.....

building reports webintelligence
1

Hi
I want to see the count of all the Accounts that are in the range of 1$ to 5000$ Amounts
I wrote this formula ,then Im getting all the Accounts (Total Number of Accounts 5045, Accounts with Amount >1 and < 5000 are 2135)
Im getting 5045, I want 2135)

=Count(If([Amount]>=1 And [Amount] < 5000 ;[Query 1].[Account]))
Pls help out.Thanks a lot.

madhu@P (BOB member since 2008-08-04)

2

Hi Madhu,

Try like this

=Count(If([Amount]>=1 And [Amount] < 5000 ;[Query 1].[Account];Distinct))

Thanks,
rave.

ravek (BOB member since 2006-08-03)

3

Thank you Ravek for the immediate reply,
Well its taking the Accounts which are >5000$ also into consideration.

Account Amount
1 500$
2 6500$

Its counting Account 2 also.Thanks a lot.

madhu@P (BOB member since 2008-08-04)

4

Hi

try this, it is working for me

=Count([Unit Tax Rate] Where ([Unit Tax Rate] > 1And [Unit Tax Rate]<5))

Thanks,
rave

ravek (BOB member since 2006-08-03)

5

Hi ,
As per you I created like this

=Count([Account] Where ([Amount] >= 1And [Amount]<5000))

It shoed me #ERROR

I think in where we have to only use Dimentions as conditions…because when I used any dimentional object as condition it showed me with out eeror when Im using measure [Amount] it showing #error.

Thanks for the help!

madhu@P (BOB member since 2008-08-04)

6

Hi Madhu,

which i created one Unit tax rate not a dimension. it is measure.

Dont know why you are getting error.

Thanks,
Ravek.

ravek (BOB member since 2006-08-03)

7

Hi Revek ,
I donno why its showing Error, in the formula you are counting the Measure depending on the same measure condition.
Is there any other way to do it… :?

Thanks so much.

madhu@P (BOB member since 2008-08-04)

8

Hi ,
In my DB Amount is a Numeric field but in the definition it is sum(nvl(table_name.Amount,0)) ,may be thats why when Im trying to write this Amount in where
Like :

=count([Account] where([Amount]>=1 And [Amount]<500))

If Im using “IF” its not getting right count ,like:

=sum(if([Amount]>=1 and [Amount]<500 ;count(1);0))

Im totally confused with no clue…

madhu@P (BOB member since 2008-08-04)

9

Try using the BETWEEN operator instead.

Patti M
Data Analyst

Patti Medina (BOB member since 2009-09-28)

10

Hi,

Try this way…

Sum(If ([Amount] >1 ForEach([Account]) And [Amount]<5000 ForEach([Account]);1;0))

Thanks!
bodevXI

bodevXI (BOB member since 2006-05-09)

11

Thank you for the post unfortunately it did not work :frowning:

I have to mention here , that I want to show the count(Number of Accounts where Amount between 1 and 5000) as a stanalone cell ,means I have to show that as count

1-5000$ Claims 5065
5001-10,000$ Claims 467
.
.
.
.
.
If I put Account Number column and this caliculated colum adjusent then its showing the Accounts which has Amount <5000 as 1 rest 0 (which I want) If I remove that Account column(All the 1’s should sum up to 2457 ) but , it shows either 0(for some formulas) or #ERROR for other formulas.

madhu@P (BOB member since 2008-08-04)

12

Hi Guys
I GOT IT.I want to share the formula with you all

Here is the Formula that worked for me

=Sum(If([Amount] Between (1;1001);1) ForEach([Account]))

I’m very much relieved .I really appreciate for all those who helped me in this.Awesome .Thank You all. :+1:

madhu@P (BOB member since 2008-08-04)