4 Replies Latest reply: Mar 20, 2018 10:38 AM by Jahanzeb Hashmi RSS

    How to calculate sum of sales based on sales plan (target) within specific dates

    Piotr Amarowicz

      Hi,

       

      I have 2 tables: one with sales data and the second one with sales plan with the sales target and dates between which the target should be reached.

       

      sales:

      Load * Inline [

      company,sales,date of sales

      Company X, 20000, 2018-01-20

      Company X, 10000, 2018-01-25

      Company X, 5000, 2018-02-02

      Company X, 15000, 2018-02-12

      ];

       

       

      sales_plan:

      Load * Inline [

      plan_id,company,date_from,date_to,sales_plan

      1,Company X, 2018-01-22,2018-02-22, 50000

      ];

       

       

      I'm failing to build a simple table which would show plan's details and sum of sales within the plan dates, something like this:

       

      Plan idCompanyPlan date fromPlan date toPlan targetSales within the planPlan realization
      1Company X2018-01-222018-02-22500003000060%

       

      Can I achieve this using Set Analysis or should I modify my data model?