Hi,
v11.1 is very impressive,

I am sorry to ask a simple question, but I would like to know how to make a running total, based on some original balance (e.g. $1,000) and a series of transactions (positive or negative). Ideally, I could create this as a data module calculation or macro, although if it can only be done in the reporting tool, then at least I can get it to the prospect.

In our simple example (attached), we have six columns:  RowID, Profit (positive or negative),  Group 1, Group 2, Group 3 (grouping columns), and Time (UTC). Assuming I have the same starting balance for each sub-group, how do I write the calculation to do this?
I understand that I can aggregate the grouping columns and the time to create a sorting column, but I can't get the calculation right. Please help!!!!!



------------------------------
brett forbes
------------------------------

#CognosAnalyticswithWatson

Well the answer is that there is a huge number of undocumented functions in the expression syntax, and sadly there is a lot more capability in the product than described by the documentation.

------------------------------
brett forbes
------------------------------

Original Message:
Sent: 11-10-2018 09:34
From: brett forbes
Subject: v11.1 How to make a cumulative sum or running total

Hi,
v11.1 is very impressive,

I am sorry to ask a simple question, but I would like to know how to make a running total, based on some original balance (e.g. $1,000) and a series of transactions (positive or negative). Ideally, I could create this as a data module calculation or macro, although if it can only be done in the reporting tool, then at least I can get it to the prospect.

In our simple example (attached), we have six columns:  RowID, Profit (positive or negative),  Group 1, Group 2, Group 3 (grouping columns), and Time (UTC). Assuming I have the same starting balance for each sub-group, how do I write the calculation to do this?
I understand that I can aggregate the grouping columns and the time to create a sorting column, but I can't get the calculation right. Please help!!!!!



------------------------------
brett forbes
------------------------------

#CognosAnalyticswithWatson

Hey Brett,

Thanks for posting your question on the community. 

The function you are looking for is called "running-total". Here is an example based on your data:

if (Sheet1.Group_ = 'P' and Sheet1.Group_2 = 'Large' and Sheet1.Group_3 = 'Red') then (running-total (Sheet1.Result_Profit_Price)) else (0)

You can create this from within the data module. Click on a column and select create calculation. 

For the initial balance, you may want to add it back in the end or create the initial balance as the starting row in your data.

Hope that helps, 


------------------------------
Sami El Cheikh
Offering Manager
IBM Cognos Analytics
------------------------------

Original Message:
Sent: 11-10-2018 09:34
From: brett forbes
Subject: v11.1 How to make a cumulative sum or running total

Hi,
v11.1 is very impressive,

I am sorry to ask a simple question, but I would like to know how to make a running total, based on some original balance (e.g. $1,000) and a series of transactions (positive or negative). Ideally, I could create this as a data module calculation or macro, although if it can only be done in the reporting tool, then at least I can get it to the prospect.

In our simple example (attached), we have six columns:  RowID, Profit (positive or negative),  Group 1, Group 2, Group 3 (grouping columns), and Time (UTC). Assuming I have the same starting balance for each sub-group, how do I write the calculation to do this?
I understand that I can aggregate the grouping columns and the time to create a sorting column, but I can't get the calculation right. Please help!!!!!



------------------------------
brett forbes
------------------------------

#CognosAnalyticswithWatson