Running Sum is a display function.
This function calculates a running sum based on records encountered so far.
The “By” statement resets its values for each group.
SELECT
QUARTER,
MONTH,
revenue,
RSUM(revenue) AS RUNNING_SUM,
RSUM(revenue BY QUARTER) AS RUNNING_SUM_BY_QUARTER
FROM
sales_subject_area
Result:
QUARTER MONTH REVENUE RUNNING_SUM RUNNING_SUM_BY_QUARTER
1 JAN 100.00 100.00 100.00
1 FEB 200.00 300.00 300.00
1 MAR 100.00 400.00 400.00
2 APRIL 100.00 500.00 100.00
2 MAY 300.00 800.00 400.00
2 JUNE 400.00 1200.00 800.00
3 JULY 500.00 1700.00 500.00
3 AUG 500.00 2200.00 1000.00
3 SEPT 500.00 2700.00 1500.00
4 OCT 300.00 3000.00 300.00
4 NOV 200.00 3200.00 500.00
4 DEC 100.00 3300.00 600.00
When your data have the same grain than the graph, you will get no problem. For instance, if you ask a week graph and that your data have only one line per week.
If your data have more than one line per week, you need to get the amount of the last line.
Unfortunately, OBIEE doesn't give the possibilitiy to set a Last aggregate rule for your column.
The following solution:
RSUM
(
CASE
WHEN RCOUNT("Date"."Year Week" BY "Date"."Year Week") = 1 THEN
SUM("Fact"."Amount" BY "Date"."Year Week")
END
)
Requisites: