Customer Forum

Total Pipeline value per month

External Posted: 2011-02-11 16:50

Hi,

I read a recent post on this but still couldn't work it out.

I've created a few reports called 'This Month Pipeline' and 'Next Month Pipeline' - exaclty the same but with different months highlighted in the criteria.

I'm now trying to create a 'totals' report that will summarise each month and give the total Amount and Pipeline Value (amount*probability). However, when asking to Summarise by Month, it just shows the first line item for that month and doesn't add them up.

A screen shot was shown on the last similar post, but I couldn't see how to get to that screen.

Many thanks,

Adam

Workbooks Support Posted: Fri, 11.02.2011 - 18:17

I would suggest the following:

In an opportunity report add the 'Amount Field' and total (sum) the field. - This will add up all the opportunity amounts into a total.
Create a calculated column called Pipeline Amount. The formula for this should be SUM(amount * probability / 100) - This will multiply the probability of each deal against the amount and dividing by 100 is required, because probability is represented as a number not a percentage. It will then total these figures.
Select the Pipeline Amount field you have just created to display as a currency.
Add the column close period - This is the close date represented against your accounting periods.
Then summarise the report by close period. - This means the report totals will be summarised by the close month giving you totals for each month.

You don't need any criteria in this report.

I hope that helps.

External Posted: Mon, 14.02.2011 - 13:11

Thanks!

External Posted: Mon, 14.02.2011 - 13:26

That helps, thanks.

Workbooks Support Posted: Tue, 15.10.2013 - 08:25

In the next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

Create a new blank report based on Opportunities. Within the Details view add a column for Object Reference, Close Period, Amount and Probability.
Select 'Add Summary View' and select 'Add grouping column'. Group by 'Close Period'.
Add a Calculated column called 'Pipeline Amount', which uses the following formula:

'SUM( PARENT('Amount') * PARENT('Probability')/ 100 )'

Check the 'Display summary' checkbox and select 'Total' from the dropdown picklist. Also, navigate to the 'Advanced' tab and display as a currency.
Add a second Calculated column called 'Total Amount', which uses the following formula:

'SUM( PARENT('Amount'))'

Check the 'Display summary' checkbox and select 'Total' from the dropdown picklist. Also, navigate to the 'Advanced' tab and display as a currency.

The report should look something like the one below (click to enlarge).