Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 id | Company | Plan date from | Plan date to | Plan target | Sales within the plan | Plan realization |
---|---|---|---|---|---|---|
1 | Company X | 2018-01-22 | 2018-02-22 | 50000 | 30000 | 60% |
Can I achieve this using Set Analysis or should I modify my data model?
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:
Please refer to this:
Try to understand it and implement it on your own; if u do find difficulties, we'll help you achieving it
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:
Thanks, works like a charm! I wasn't familiar with IntervalMatch - very ufesul function.
You can do that with set analysis
Sum({<date_of_sale={">=date_from=<Today()"}>}sales)