Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

piotrama
New Contributor II

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:

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?

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

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:

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

];

Inner Join

IntervalMatch ( "date of sales", company )

LOAD date_from, date_to, company

Resident sales_plan;

Result:

Capture.PNG

4 Replies
OmarBenSalem
Esteemed Contributor

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

Please refer to this:

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

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

OmarBenSalem
Esteemed Contributor

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:

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

];

Inner Join

IntervalMatch ( "date of sales", company )

LOAD date_from, date_to, company

Resident sales_plan;

Result:

Capture.PNG

piotrama
New Contributor II

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.

zebhashmi
Valued Contributor

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)

Community Browser