empty cell with Runningcount

Hi,

I’m using the runningcount function but I have a problem with empty values. Here are the details:
My data is showing the arrival rate of problems by module by month by release.
So I defined a variable = runningcount(; ). And I’m using it on a graph with the month on the X axis, the release on the Z axis, and on Y.

The problem is that for some months, there are no problems for some module.
So the graph takes a dive for these months because these module show as 0.
How can I force BO to keep the previous runningcount when it’s 0 for this month?

thanks,
Benoit


benoitd (BOB member since 2004-06-30)

Benoit,

It sounds like the data is returning nulls rather than zeros.
You may need to either populate your database with extra rows for these zero entries or build an isnull/nvl statement into your measure to cater for zero e.g. in Oracle


nvl(column,0)

and SQL Server


isnull(column,0)

HTH,
Mark

Hi,

thanks for the reply, but I think I wasn’t clear…

There are gaps in the table resulting from the runningcount. Let me give you an example (for simplicity, I didn’t use the release in the table)

Month module # of problem
1 M1 3
1 M2 1
2 M1 5
3 M1 2
3 M2 3

so the runningcount(;) by month will give
Month Module RunningCount
1 M1 3
M2 1
2 M1 8
M2 <<<< that’s were I have a blank because there are no problem in Month 2 for module M2
3 M1 10
M2 4

So if I try to put that on a stack bar chart, on Month 2, M2 takes a dive.

I cannot add data in the database. I don’t control it. All I need is for the runningcount to stay at it’s previous value if there are no entries.

thanks


benoitd (BOB member since 2004-06-30)

Benoit,

That’s exactly the situation that I thought.
If the modules are listed in a separate table, you could use an outer joins and a case or nvl statement like


SELECT
  CASE WHEN Benoit.Problems IS NOT NULL THEN Benoit.Problems ELSE 0 END,
Benoit2.Module_ID,
Benoit3.Month_No
FROM
Benoit,
Benoit2,
Benoit3
WHERE
  ( Benoit2.Module_ID*=Benoit.Module_Code  )
  AND  ( Benoit.Month_No=*Benoit3.Month_No  )

to produce


M1        
Month No  Problems    RunningSum
1              3               3
2              5               8
3              2               10
M2        
Month No	Problems	RunningSum
1              1               1
2              0               1
3              3               4

Regards,
Mark

thanks again,

but again I think I didn’t explain the problem quite correctly :oops:

What you call “Benoit.Problems”, which represent the # of problem by module by month, is not really a # of problem, it the problem itself. So the first table in my example should have been

Month module problem
1 M1 P1
p2
p3
M2 p4
2 M1 p5

p9
3 M1 p10
p11
M2 p12
p13
p14

So I cannot use the select you shown.

Thanks a lot for your help, and sorry for the mistake in the problem description. It just show that, even if I’ve been using BO for 1 year, I still have a lot to learn :?


benoitd (BOB member since 2004-06-30)