Forum Discussion

VidhayV's avatar
VidhayV
Qrew Member
6 years ago

Summarize data from one table with requirements from another table

Hello,

I have two tables that are not linked.
Sales
Promo Line Items

The sales table has the sales data (sku, price, date, qty)
The promo line items has details of the promos (sku, promo price, promo start date, promo end date)

I am looking for a way to get the sum of qty sold with the filters sku, promo price, start date, end date applied)

For example, sku CAR would have 50 entries in the sales table between Jan 1st and Jan 31st with prices ranging from $100-$105.
sku CAR is also there in the promo line items as CAR, $101.50, Jan 10th, Jan 12th.
I would like to get a report from the sales table based on the fields in the promo line items.

Thank you
reports and charts
Tables and fields
Tips and tricks
  • VidhayV's avatar
    VidhayV
    Qrew Member
    6 years ago
    Any update on this?

    Thank you

    ------------------------------
    Jay
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      I doubt that it's going to be possible to just run a report of all promotions showing their sales because in fact there can be competing overlapping promotions applying to the same sales.

      But here is an approach where you can look at the sales for a particular promotion.

      Create a new tale called Promo Results with 1  and only 1 record in it.  Make a relationship to the promo table with the Promo table being the Parent. 

      Select a promo and lookup the relevant promo details.  The Promo Results now knows those details.

      Make a relationship to Sales where 1 Promo result has Many Sales and set the reference field on the right side of the relationship to be a formula field called Link to all sales with a formula of 1. 

      Lookup all the various promo details down to Sales.

      Make a report of sales which meet the Promo Results lookup fields. Call that report Promo Results.  BUt on fact I strongly suggest that you make a formula checkbox field called "Sales meets selected Promo Results criteria" 

      On the promo results form put the Report Link field for sales on the form and show thee results on the form.  Set the form to use the Report called Promo Results.  You can make a summary field to show the total sales.

      The Promo results form will now show all the sales. 

      Once that its working you can put icng on your cake by making a URL formula button which when clicked on a promo record, will edit the Promo results record to load that Promo and then display the promo results record.  So then its just 1 click from a promo off a URL button called show Promo results and you will see the sales.  

      Note also that you don't need to limit the Promo results to just showing a list of the sales.  You can include that same reports link field on the Promo results form many times, and show say sales charts to see how the promo unfolded over time.

      ------------------------------
      Mark Shnier (YQC)
      markshnier2@gmail.com
      ------------------------------