Count based on a condition across the rows

Hi All,

I’m having two problems in two different reports. I’d really appreciate your help.

  1. A have a column in a report with the number of days between 2 dates.
    I want to count the number of days > 45 and the number of days < 45 and based upon these counts I have to do a calculation.
    My understanding is that it should be done using a self join in the universe. Please let me know if this could be done at the report level.

  2. I am sure this is very simple and I dont know what I am missing.
    I have a master-master-detail report. (sub-sections with in section). I performed sum and average on each sub-sections and it is working fine.
    When i try to drag the values of sum and average from the footer to the sub-section area, I get a different result than what appears at the footer. (I think this is what it is doing…it does not include duplicates for calculating the sum and average in the cell).

Thanks for any help.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Prob 1 :

Create a variable which check for day difference value.

Var = if DayDiffCol >45 then 1 else 0

Now in your calculation variable use Var.

CalcVar = if ( Var=1 then “X” else “Y”)

Prob 2 :
Sum and Average at the report footer is showing the sum all th data in that section.
Use View structure and see where u have put Sum and Average variables.

Hope it helps


JaiGupta (BOB member since 2002-09-12)

Jai,

Thanks alot for your reply. I did the 1/0 calculation earlier but how would I be able to count the number of times 1 appear in that column.
My understanding is that you can calculate across the columns in BO reporter, but you cant perform calculations down the row ; unless you are using simple aggregate fns or you can use pl/sql.

Now, lets say that I have a column with

1
1
1
0
0
1
0

How would I be able to get
Count of Ones = 4
Count of Zeroes = 3

Thanks for your help again.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

  • create a variable a0 with formula
    = If =0 Then 1

  • create a variable a1 with formula
    = If =1 Then 1

    where F1 is the column with 1/0 values

  • Sum() and Sum() should give you the desired output


prasad :india: (BOB member since 2002-08-19)

Thanks Prasad,

This was a nice idea and I could get a count now using Sum on the column. Ultimately I wanted the percentage of cases in which the case was handled in 45 days…ie percentage of 1’s and percentage of 0’s (if the case is not handled in 45 days).

Using Sum as the aggregate on the report column allows me to get the count, and it creates a formula. When i try to calculate the above percentages and display the result in a cell, it does not calculate and instead shows “#COMPUTION”.

Any suggestions on that.

Thanks for your idea and help.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

If you create a variable with following formula

=Count(RowIndex()) ForAll (All dimensions in block)

it will give you number of total rows in the block.
Then you can use Sum()/ to calculate the percentages.


mkumar (BOB member since 2002-08-26)

Thanks Kumar,

It worked. Thanks alot Prasad and Jai.
You guys are great.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Hi

I am having a similar case where I need to sum the values of 1 and 0 separately

For Example

Yes/No (Dimension Field)

1
1
1
0
1
0
0

I need the result to be number of 1s = 4 and number of zeroes =3. I tried the solution mentioned above and am getting number of 1s to be 1 instead of 4. And if I say

Count(<Yes/No>) Where (<Yes/No>=“1”), I am getting #IERR error in a free standing cell. Kindly help. Thanks.

Rahul.


rahul80 (BOB member since 2005-01-30)

Try this:

sum(if <yes/no> = 1 then 1 else 0)

sum(if <yes/no> = 0 then 1 else 0)


Mahi (BOB member since 2004-07-22)

Thanks for the input Mraty. I tried the suggestion in a free standing cell and getting the #IERR error message again. Please let me know if there is any other means of doing this calculation. Thanks.

Rahul.


rahul80 (BOB member since 2005-01-30)