Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How do I Summarise Weekly Sales by Product


swan
 Share

Recommended Posts

Hi I have table of sales orders and a separate table of products.

 

The sales order's table also contains the sales items and their qty.

 

In the Products table I have created a portal view that summarises the per weekly quantity sold of each product on their respective product record.

 

My problem is:

 

that I cannot seem to find away to use this information other than to view it.

 

If I export the weekly sales by product either seems to be missing or simply produces the the total sales per product rather than per product per week, or if I were to export from the sales Order table I would end up exporting out every record, which would be far more information than I need.

 

 

How can I:

 

Export or retain for use in calculations only 1 record per Week per Product that contains the qty sold in this period.

 

so it would look like:

 

[TABLE=width: 500]

[TR]

[TD]Product_ID[/TD]

[TD]PrevWeekBucket[/TD]

[TD]EndWeekDate[/TD]

[TD]QtySold[/TD]

[/TR]

[TR]

[TD]001[/TD]

[TD]0011[/TD]

[TD]28/02/12[/TD]

[TD]3

[/TD]

[/TR]

[TR]

[TD]001[/TD]

[TD]0012[/TD]

[TD]21/02/12[/TD]

[TD]14[/TD]

[/TR]

[TR]

[TD]001[/TD]

[TD]0013[/TD]

[TD]14/02/12[/TD]

[TD]5[/TD]

[/TR]

[TR]

[TD]002[/TD]

[TD]0021[/TD]

[TD]28/02/12[/TD]

[TD]79[/TD]

[/TR]

[TR]

[TD]002[/TD]

[TD]0022[/TD]

[TD]21/02/12[/TD]

[TD]40[/TD]

[/TR]

[TR]

[TD]003[/TD]

[TD]0031[/TD]

[TD]28/02/12[/TD]

[TD]34[/TD]

[/TR]

[TR]

[TD]003[/TD]

[TD]0032[/TD]

[TD]21/02/12[/TD]

[TD]14[/TD]

[/TR]

[TR]

[TD]003[/TD]

[TD]0033[/TD]

[TD]14/02/12[/TD]

[TD]57[/TD]

[/TR]

[TR]

[TD]003[/TD]

[TD]0034[/TD]

[TD]07/02/12[/TD]

[TD]28[/TD]

[/TR]

[/TABLE]

 

Many thanks

 

Sam

Link to comment
Share on other sites

Assuming that each record in the sales order table is the record of ONE individual product being sold, the quantity sold, the date it was sold, and perhaps other info such as the price per item and the extended price (price per item times quantity) and so forth...

 

If you don't already have a field for EndWeekDate calculated from the sales order record's sales date field, create one, using WeekOfYearFiscal (sales date; when your "year" starts) and doing the math in a formula to obtain the EndWeekDate and PrevWeekBucket based on which week the sale occurred in; or, alternatively, have a table of weeks in which each record has a start date, end date, week number. I don't know what PrevWeekBucket actually is, but presumably it's a variation on week number. If you go with the table-of-weeks approach, create a relationship from Sales Order to Weeks like so:

 

Sales Order........Weeks

 

Date ≥ StartDate AND

Date ≤ EndDate

 

... and then create a field in Sales Order that looks up or auto-enters a calculated value for PrevWeekBucket based on the Week Number of the related Weeks record. Also look up EndWeekDate.

 

Create also a summary field defined as Total of Qty Sold, let's call it SummQtySold.

 

Create a new layout that shows records from the table sales order. Type of layout will be list/report. You want a "Report with grouped data" with subtotals. (Grand totals too if you want 'em). Fields you want are Product_ID, PrevWeekBucket (the above-described calculation or lookup field), and EndWeekDate (also described above). On the Organize Records by Category screen specify you want to summarize by Product ID and then by PrevWeekBucket or EndWeekDate (choose one). Sort Order: will be by those two fields, leave it that way. For Specify Subtotals you want to specify SummStySold, below record group. Pick an appearance, DO let it create a script for you, and exit out of the New Layout wizard into layout mode. You'll end up with a subsummary part when sorted by PrevWeekBucket (or EndWeekDate) and a second subsummary part when sorted by Product ID, then a body part. If you subsummarized by PrevWeekBucket, EndWeekDate will be in the Body part, or the other way around if you subsummarized by EndWeekDate; whichever field is in the Body part, slide it up into the other field's subsummary part Go to Part Setup (in the Layouts menu) and nuke the Body part (you will not need it).

 

For any given found set of records (let's say, all records in sales orders where date is in the range 1/1/2012...1/28/2012), this report will look like this when the records are sorted by PrevWeekBucket and EndWeekDate:

 

Product ID..... EndWeekDate...... Qty Sold

 

001

....................1/7/2012................3

....................1/14/2012..............13

....................1/21/2012...............6

....................1/28/2012..............19

002

....................1/7/2012................4

....................1/14/2012..............13

....................1/21/2012...............7

....................1/28/2012..............10

003

................... etc

 

Link to comment
Share on other sites

Thanks, that's great.

 

I realised I couldn't get it to work due to not considering the relationships properly (I find this aspect of filemaker not as transparent as I'd like having made several mistakes as a result of this!)

 

As I had a summary field already for the QtySold once I'd corrected the table relationships and sorted the data by the Product_ID & PrevWeekBucket I was able to produce an export of total qty sold by week on each product.

 

This being the first time I used the summary fields I didn't realise that when you've sorted the data correctly with the right table relationships adding the summary field to your export automatically generates the additional summary fields for the fields you've grouped the data by.

 

For explanation, the PrevWeekBucket is a moving unique field that concats the Product_ID and WeekNo where the WeekNo is stationary against the current date instead of a fixed date in time and always displays complete weeks. This seemed like an easier way to always ensure that each week's sales was comparable without having to extrapolate sales for the remaining week and also allows have constant references for current, previous week etc.

 

I presume in a production environment this would not be a good way of going about it due to it's inherent additional processing requirements however as I use filemaker for personal ad hoc workaround solutions it's an easy way of achieving the info I want.

 

Many thanks,

 

Sam

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use