Running Total

[V i n c e n t]

Hi,

I need your help to do a running-total

I want that :
MONTH CUSTOMER REVENUE  RUNNING-TOTAL
    01         APPLE       5000              5000
    02         APPLE          0                 5000

I have that :
MONTH CUSTOMER REVENUE  RUNNING-TOTAL
    01         APPLE       5000              5000

There is no value for this customer in the second month, then the running-total dont work with something who don't exist.

How to do what i want ??

Thanks for helping me.

[blom0344]

If the row is brought in from the query, then try to coalesce the value to change null to 0 and perform the running total on this expression

[V i n c e n t]

Can you explain in more détail please ?

[calson33]

You will need to join your results set with a query that returns a list of months.

"MONTHS LIST"	"DATASET MONTH"	"DATA"
1	1	5000
1	- (COALESCE(DATA,0)	5000 (Running total)

[V i n c e n t]

Thanks Calson33, i'll try this tomorrow

[blom0344]

running totals are generated by the Cognos server engine, not within the SQL executed. So if you want to add non-existing rows, then you need to do a little more than use a list of months (otherwise you will get an additional row with a null value for the customer.

There is an elegant solution using a union : bring in all combinations of month and customer (with a crossjoin) in a second query with a zero for the revenue. Union this with the original query and use the result set to calculate the running total