Product Category Totals on Business Day Recap
I have a FMP13 solution that is used to import various daily reports and then combine them for daily/weekly/monthly recap reports. The part that I'm having trouble with is in adding a section to show totals for product categories on a daily recap. There are 4 tables at play in this:
Business Days (Combines numbers for each day)
Product Sales Totals by Day (a join between Business Days and the Products Table) (i.e. on 5/1/15 Product A sold 50 widgets and produced $500)
Products (Each product that is offered)
Product Categories (Groups various Products together)
I'm developing a daily recap report that is based on the Business Days table that will include a portal for the top 10-15 product categories with various stats. The problem I'm running into is that since the Business Days table is related to the Product Sales Totals Table by a BusinessDayID, I can easily list daily product sales, but I can't find a way to then calculate daily sales totals for each product category (with the way I have it setup, if a product is sold on a given day, I'm going to get the lifetime sales total for the product category).
I appreciate any help and will provide additional info if this seems confusing!
Thank you in advance!