Customer Platform monthly report - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Joanne ArnoldJoanne Arnold 

Customer Platform monthly report

I have a requirement to build a report to show how many customers have purchased by month and value, this needs to be broken down by account owner to show their individual totals.
Best Answer chosen by Joanne Arnold
Nirav ShahNirav Shah
Hi Joanne
There is an idea for "Report Option for Distinct/Unique Records (https://success.salesforce.com/ideaview?id=08730000000BrYLAA0)"

But as a workaround, you can do following

Create a custom field on each of the objects (I would do this on every object you use in SFDC).  (in your case Invoice object)

Field Name: whatever you want 
Datatype: Formula 
Result:  Number,0 decimal
Formula:  1  (that's it, just a number 1)

now add these custom fields to your report and Summarize(Sum) them.  You'll get the count of distinct records for each object.

Hope this helps
Thanks
Nirav

All Answers

Choon Soon GanChoon Soon Gan
Hi Joanne, I think you are looking for a matrix report. 

Drop the account owner on rows and Closed Date (Group by calendar month) and sum the amount.

Feel free to let me know if you need further visualisation 
Nirav ShahNirav Shah
Hi Joanne,
Can you brief, Where you exactly store all your Purchase Data? and how it related with Account?

Thanks
Nirav
Joanne ArnoldJoanne Arnold
Hi 
We store our purchase data in invoices (invoices with lines)and is tied back to the account by account number.
Joanne
Nirav ShahNirav Shah
Hi @Joanne,
Thann you can create a Matrix report with the Report Type which includes both Accounts and Invoice.

You can make the Report Format as Matrix.
On the Row Level Grouping add the Account Owner, On the Column level Grouping add Purchase date (Group it by Calendar Month) add the value as Sum

Thanks
Nirav
Joanne ArnoldJoanne Arnold
The way our invoices are loaded it has produced a recod for each customer so for instance within calender month one the customer X has had 5 invoices, customer Y has had 2 invoices  so the report has counted this as 7 records, but in fact I only want it to count the customer's so the record count should be 2.
Many thanks
Nirav ShahNirav Shah
Hi Joanne
There is an idea for "Report Option for Distinct/Unique Records (https://success.salesforce.com/ideaview?id=08730000000BrYLAA0)"

But as a workaround, you can do following

Create a custom field on each of the objects (I would do this on every object you use in SFDC).  (in your case Invoice object)

Field Name: whatever you want 
Datatype: Formula 
Result:  Number,0 decimal
Formula:  1  (that's it, just a number 1)

now add these custom fields to your report and Summarize(Sum) them.  You'll get the count of distinct records for each object.

Hope this helps
Thanks
Nirav
This was selected as the best answer
Joanne ArnoldJoanne Arnold
Hi That is great it has worked. is it possible to compare months so to have the ability to show 2016 month 1 and 2017 month 1 and so on a the report.

User-added image

Many thanks