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.
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
Can you explain in more détail please ?
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) |
Thanks Calson33, i'll try this tomorrow
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