Hello Cognos-user,

I'm using Cognos 11.1 and I'm trying to get a cumulative sum in my report.

We habe  a database for our Projects. One of the tables contains the expected payments (on Hardware, services, ...):
Project_Id, Payment_Id, Planned_Payment_amount, Planned_Payment_date, realised_Payment_amount, realised_Payment_date
In the beginning of the project, all planned Payments are sceduled (column 3 and 4), during the project realised payments are put in the tabel (column 5 and 6).

I can find out, how much we have paid, I can find out how much we have planned to pay, I can find out how much we have planned to pay until a given date (1st of June 2021).

But what I can't find out is the following: for every planned Payment (Payment_Id) I would like to know how much we have planned to pay in this project untill that planned_payment_date, in other words the cumulative sum until then.

Could anyone help me with that (either in Cognos or as SQL)? 

Thanks in Advance

------------------------------
Ludwig Leijten
------------------------------

#CognosAnalyticswithWatson

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Thanks a lot,
I found that in the forum, tried it, but it didn't seem to work.
Now I tried it again, and it works.
But youtr answer was exactly what I needed to try it again!


------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Okay, that's usefull, as it shows that we misunderstood each other.
As I understand it now, running-total depends on the output of the query (and then, indeed, it will depend on the order of the output):
What I was looking for, would only depend on the data in the Databank itself, not so much on the output.
So, the output of my query would be all planned payments in the next 2 months, the "cumulative sum" would be the sum of all payments uptill this payment (so also including the payments before now, that are not included in the output).
Example:
date              amount                 Cumulative sum
15.4.               1.000 €              1.000 €
15.5.               1.000 €              2.000 €
15.6.               1.000 €              3.000 €
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €
15.9.               1.000 €              6.000 €

Output of the query "Payments in the next two months":
date              amount                 Cumulative sum
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €

Of course, in this case with the running total I can have a work-around (taking the running-total of the query and adding the sum of the payments before today), but for some other cases I would be interested in solving the above question.

Regards

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Thu June 24, 2021 11:04 AM
From: Robert Dostal
Subject: cumulative sum

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------


Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Hi Ludwig,

now I get to the point where it gets tricky ;)
The problem with a regular filter in the query would be, that you cut out the previous data that is needed to compute the right cumulative sum for the upcoming payments.
Why don't you use two queries that are joined together? One just with the absolute planned payments for the  next two motnhs and another query with all payments but accumulatied. Then join on projectID and Date:

Base query

Cumulative query:

Joined query:





------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Fri June 25, 2021 03:24 AM
From: Ludwig Leijten
Subject: cumulative sum

Okay, that's usefull, as it shows that we misunderstood each other.
As I understand it now, running-total depends on the output of the query (and then, indeed, it will depend on the order of the output):
What I was looking for, would only depend on the data in the Databank itself, not so much on the output.
So, the output of my query would be all planned payments in the next 2 months, the "cumulative sum" would be the sum of all payments uptill this payment (so also including the payments before now, that are not included in the output).
Example:
date              amount                 Cumulative sum
15.4.               1.000 €              1.000 €
15.5.               1.000 €              2.000 €
15.6.               1.000 €              3.000 €
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €
15.9.               1.000 €              6.000 €

Output of the query "Payments in the next two months":
date              amount                 Cumulative sum
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €

Of course, in this case with the running total I can have a work-around (taking the running-total of the query and adding the sum of the payments before today), but for some other cases I would be interested in solving the above question.

Regards

------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Thu June 24, 2021 11:04 AM
From: Robert Dostal
Subject: cumulative sum

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

And that's how I did it in the end.
Thanks again!

It brought up a question, I posed before though:
"One question left though:
how does Cognos "know" what the correct order is?"

Apparently, in this extra query, it uses the order of the table with the payments, ordered by the ID (which is identical to the chronological order of writing them in the table). But sometimes, this is not identical to the order of the date of paying.
ID               (planned) Date of Payment              Amount
1234             1.6.2021                                         1.000
1235             1.8.2021                                         1.000
1236             1.9.2021                                         1.000
1533             1.7.2021                                         1.000
Now for ID 1533 it shows a cumulative sum of 4.000 instead of 2.000.

Can I change the order Cognos uses for the table in the Database? I only know how to change the order in lists that I have as output, but I don't want to print this information.

Hope, I don't have to bother you too often any more :-)


------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Mon June 28, 2021 04:07 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

now I get to the point where it gets tricky ;)
The problem with a regular filter in the query would be, that you cut out the previous data that is needed to compute the right cumulative sum for the upcoming payments.
Why don't you use two queries that are joined together? One just with the absolute planned payments for the  next two motnhs and another query with all payments but accumulatied. Then join on projectID and Date:

Base query

Cumulative query:

Joined query:





------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------


Original Message:
Sent: Fri June 25, 2021 03:24 AM
From: Ludwig Leijten
Subject: cumulative sum

Okay, that's usefull, as it shows that we misunderstood each other.
As I understand it now, running-total depends on the output of the query (and then, indeed, it will depend on the order of the output):
What I was looking for, would only depend on the data in the Databank itself, not so much on the output.
So, the output of my query would be all planned payments in the next 2 months, the "cumulative sum" would be the sum of all payments uptill this payment (so also including the payments before now, that are not included in the output).
Example:
date              amount                 Cumulative sum
15.4.               1.000 €              1.000 €
15.5.               1.000 €              2.000 €
15.6.               1.000 €              3.000 €
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €
15.9.               1.000 €              6.000 €

Output of the query "Payments in the next two months":
date              amount                 Cumulative sum
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €

Of course, in this case with the running total I can have a work-around (taking the running-total of the query and adding the sum of the payments before today), but for some other cases I would be interested in solving the above question.

Regards

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 11:04 AM
From: Robert Dostal
Subject: cumulative sum

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Hi @Ludwig Leijten
​that should be done by applying a pre-sorting on the underlying query on the specific data item:

IBM Cognos then pushes this sort option down to the sub-query:
Tested with the IBM samples and it works smoothly.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------

Original Message:
Sent: Thu July 01, 2021 11:17 AM
From: Ludwig Leijten
Subject: cumulative sum

And that's how I did it in the end.
Thanks again!

It brought up a question, I posed before though:
"One question left though:
how does Cognos "know" what the correct order is?"

Apparently, in this extra query, it uses the order of the table with the payments, ordered by the ID (which is identical to the chronological order of writing them in the table). But sometimes, this is not identical to the order of the date of paying.
ID               (planned) Date of Payment              Amount
1234             1.6.2021                                         1.000
1235             1.8.2021                                         1.000
1236             1.9.2021                                         1.000
1533             1.7.2021                                         1.000
Now for ID 1533 it shows a cumulative sum of 4.000 instead of 2.000.

Can I change the order Cognos uses for the table in the Database? I only know how to change the order in lists that I have as output, but I don't want to print this information.

Hope, I don't have to bother you too often any more :-)


------------------------------
Ludwig Leijten
------------------------------


Original Message:
Sent: Mon June 28, 2021 04:07 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

now I get to the point where it gets tricky ;)
The problem with a regular filter in the query would be, that you cut out the previous data that is needed to compute the right cumulative sum for the upcoming payments.
Why don't you use two queries that are joined together? One just with the absolute planned payments for the  next two motnhs and another query with all payments but accumulatied. Then join on projectID and Date:

Base query

Cumulative query:

Joined query:





------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Fri June 25, 2021 03:24 AM
From: Ludwig Leijten
Subject: cumulative sum

Okay, that's usefull, as it shows that we misunderstood each other.
As I understand it now, running-total depends on the output of the query (and then, indeed, it will depend on the order of the output):
What I was looking for, would only depend on the data in the Databank itself, not so much on the output.
So, the output of my query would be all planned payments in the next 2 months, the "cumulative sum" would be the sum of all payments uptill this payment (so also including the payments before now, that are not included in the output).
Example:
date              amount                 Cumulative sum
15.4.               1.000 €              1.000 €
15.5.               1.000 €              2.000 €
15.6.               1.000 €              3.000 €
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €
15.9.               1.000 €              6.000 €

Output of the query "Payments in the next two months":
date              amount                 Cumulative sum
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €

Of course, in this case with the running total I can have a work-around (taking the running-total of the query and adding the sum of the payments before today), but for some other cases I would be interested in solving the above question.

Regards

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 11:04 AM
From: Robert Dostal
Subject: cumulative sum

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Perfect, thanks

------------------------------
Ludwig Leijten
------------------------------

Original Message:
Sent: Thu July 01, 2021 11:33 AM
From: Robert Dostal
Subject: cumulative sum

Hi @Ludwig Leijten
​that should be done by applying a pre-sorting on the underlying query on the specific data item:

IBM Cognos then pushes this sort option down to the sub-query:
Tested with the IBM samples and it works smoothly.

Br

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------


Original Message:
Sent: Thu July 01, 2021 11:17 AM
From: Ludwig Leijten
Subject: cumulative sum

And that's how I did it in the end.
Thanks again!

It brought up a question, I posed before though:
"One question left though:
how does Cognos "know" what the correct order is?"

Apparently, in this extra query, it uses the order of the table with the payments, ordered by the ID (which is identical to the chronological order of writing them in the table). But sometimes, this is not identical to the order of the date of paying.
ID               (planned) Date of Payment              Amount
1234             1.6.2021                                         1.000
1235             1.8.2021                                         1.000
1236             1.9.2021                                         1.000
1533             1.7.2021                                         1.000
Now for ID 1533 it shows a cumulative sum of 4.000 instead of 2.000.

Can I change the order Cognos uses for the table in the Database? I only know how to change the order in lists that I have as output, but I don't want to print this information.

Hope, I don't have to bother you too often any more :-)


------------------------------
Ludwig Leijten


Original Message:
Sent: Mon June 28, 2021 04:07 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

now I get to the point where it gets tricky ;)
The problem with a regular filter in the query would be, that you cut out the previous data that is needed to compute the right cumulative sum for the upcoming payments.
Why don't you use two queries that are joined together? One just with the absolute planned payments for the  next two motnhs and another query with all payments but accumulatied. Then join on projectID and Date:

Base query

Cumulative query:

Joined query:





------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Fri June 25, 2021 03:24 AM
From: Ludwig Leijten
Subject: cumulative sum

Okay, that's usefull, as it shows that we misunderstood each other.
As I understand it now, running-total depends on the output of the query (and then, indeed, it will depend on the order of the output):
What I was looking for, would only depend on the data in the Databank itself, not so much on the output.
So, the output of my query would be all planned payments in the next 2 months, the "cumulative sum" would be the sum of all payments uptill this payment (so also including the payments before now, that are not included in the output).
Example:
date              amount                 Cumulative sum
15.4.               1.000 €              1.000 €
15.5.               1.000 €              2.000 €
15.6.               1.000 €              3.000 €
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €
15.9.               1.000 €              6.000 €

Output of the query "Payments in the next two months":
date              amount                 Cumulative sum
15.7.               1.000 €              4.000 €
15.8.               1.000 €              5.000 €

Of course, in this case with the running total I can have a work-around (taking the running-total of the query and adding the sum of the payments before today), but for some other cases I would be interested in solving the above question.

Regards

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 11:04 AM
From: Robert Dostal
Subject: cumulative sum

I would say that depends on the sorting options of your report list. See sample attached.
This list is sorted by the month name instead of the numbers and therefore the running-total gets computed by that order.

You can also try the "at" option for the running-total function if you know what you're doing ;).

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen


Original Message:
Sent: Thu June 24, 2021 10:49 AM
From: Ludwig Leijten
Subject: cumulative sum

Hi, I implemented it and it works.
One question left though:
how does Cognos "know" what the correct order is?
I want the order to be the dates, which does not have to be the order of the Payment_Id's. It might be, that during the project I include a new planned payment. Then the ID will be larger than the already existing planned payments, but the date might be earlier.
Can one influence this order-definition?
Thanks in advance

------------------------------
Ludwig Leijten


Original Message:
Sent: Thu June 24, 2021 09:13 AM
From: Robert Dostal
Subject: cumulative sum

Hi Ludwig,

I'd suggest to use the running-total function in a report. It lets you specify on what element/column you want a running total. See sample attached. There's the actual revenue, the cumulative revenue by region and the cumulative revenue for the whole report:

I used the followeing calculations:

and

The rest of the report should be regular filtering depening on what you'd like to present.

I hope this helps you.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen