How to summarize revenue per customer by product type

[Juerg]

Hi all,

 

I am after some help with summarizing some data using the attached relationship structure.

 

 

I need to show a summary of revenue per product on the Customer table and would prefer to show this as a bar chart. The problem I am having is summarizing each unique product type; I can show an overall summary for all products but I need a summary per individual product type (there are 54!!!)

 

I've seen this done with separate TOs for each Product Type but with 54 and the possibility of more in the future I'm looking for a scalable solution.

 

Anyone got any bright ideas?

 

Many thanks,

 

Juergen

 

 

 

 

 

[Matthew F]

Why am I not seeing 'product type' in your tables?

 

Do you really want a separate bar chart for each product for each customer?  I'm not sure how that would be scalable if you add new products.  

 

What if you just have a single bar chart that shows one product at a time, as specified by the user?  For example, the user would chose a product in a new field (product choice) formatted as a checkbox or pop-up, and then your solution would generate a bar chart for this product.  The solution would limit the products displayed by a relationship that includes both product type and the customer ID.   

[Juerg]

My mistake. The tables show JOB when it should say product. Wherever you see the word Job in the tables it should say Product! The idea is that I would like to be able to monitor spending habits per customer. If I can see a Bar chart then it will help to determine which products to promote to that customer (the ones they aren't ordering!) Does that make sense? Another approach I've thought of is to write a script which loops though the jobs for the current customer and stores the data in a hidden field which could then be used as the data for the y-axis of the bar chart using delimited values. That would perhaps be more scalable. How does that sound? Thanks Juerg

[No_access]

This is actually pretty simple but it does take a little prep on your part.

My solution, was to create a seperate table, see screenshot below. This table is simply a bunch of sub-summary parts

 

1) I export the data I need.

2) delete all data in the table for the report

3) import new data

then a buttons for different sort options,

 

 

the key to this report is the repeating summary and repeating calculation fields.  Note the use of EXTEND. 

 

Case( Get( CalculationRepetitionNumber ) = Right(Year ( Extend(ShipDate ));1) ;
  Sum(Extend( ItemPrice by ProductGroup2 ) ))
 

If you upload a empty file with some sample data I would be glad to help you with this. it sounds more complicated than it is.

 

 

 

hope this helps.