Print Page - Running total or something like that !

Title: Running total or something like that !
Post by: V i n c e n t on 06 Jun 2012 07:29:24 AM

Hi,
I need your help to solve my problem, can someone help me ?

I want that

FACT MONTH | CUSTOMER+PRODUCT LINE | CUMULATIVE CUSTOMER + PRODUCT LINE
-----------------------------------------------------------------------
1 | 16 016 | 16 016
-----------------------------------------------------------------------
2 | 15 302 | 22 432
-----------------------------------------------------------------------
3 | 16 837 | 27 883
-----------------------------------------------------------------------
4 | 15 347 | 31 382
-----------------------------------------------------------------------

and i have that

FACT MONTH | CUSTOMER+PRODUCT LINE | CUMULATIVE CUSTOMER + PRODUCT LINE
-----------------------------------------------------------------------
1 | 16 016 | 16 016
-----------------------------------------------------------------------
2 | 15 302 | 31 318
-----------------------------------------------------------------------
3 | 16 837 | 48 155
-----------------------------------------------------------------------
4 | 15 347 | 63 502
-----------------------------------------------------------------------

I'm working with a data warehouse (relational) and i want to do a cumulative total month by month of couples customers+ product line without recounting the couples already counted in previous months.
The running-total function do the cumulative total of any month with recount.
I try the distinct fonction but dont work.

Any idéa ? can someone help me ?

Thanks

Title: Re: Running total or something like that !
Post by: blom0344 on 08 Jun 2012 06:46:34 AM

You could solve this by adding an additional query that determines the highest value of month for the combination of customer/productline from the datawarehouse.

Use this query in a join with the orginal one , on customer/productline and month to retain only the combinations with the highest month.

Use the output of this joined set as the input for a query that computes the cumulatives.

Instead of month a better solution would to use the period , like in YYYYMM , say 201206. Month itself is not unique , which may cause problems if you need data transcending years.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: V i n c e n t on 06 Jun 2012 07:29:24 AM