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

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

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:

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:

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?

• ###### Re: How to calculate sum of sales based on sales plan (target) within specific dates

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/IntervalMatch.htm

Try to understand it and implement it on your own; if u do find difficulties, we'll help you achieving it

• ###### Re: How to calculate sum of sales based on sales plan (target) within specific dates

Here's the solution(but try to do it on ur own):

sales:

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:

plan_id,company,date_from,date_to,sales_plan

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

];

Inner Join

IntervalMatch ( "date of sales", company )

Resident sales_plan;

Result:

• ###### Re: How to calculate sum of sales based on sales plan (target) within specific dates

Thanks, works like a charm! I wasn't familiar with IntervalMatch - very ufesul function.

• ###### Re: How to calculate sum of sales based on sales plan (target) within specific dates

You can do that with set analysis

Sum({<date_of_sale={">=date_from=<Today()"}>}sales)