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?
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
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.
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 )
but again I think I didn’t explain the problem quite correctly
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
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 :?