Want to show Scheme wise sales for a particular period

[tripatiqv]

Hi,

I have 2 table one is scheme table  and sales table

In scheme table  for a particular material scheme start date and end date is available

in Sales table for a particular material sales will available date wise

My requirement is

if I select Mar 2020,
Then for Material code M001 the sales between schme period to be shown

for ex  sum(Sales)  between Feb 1 to Feb 14 and between Mar 4 to Mar 18 to be shown

7680.22+8184.50=15864.72

Because Scheme run period for Material M001 is Feb 1 to Feb 14
and Mar 4 to Mar 18

 I have attached source file and qlikview file.

[lironbaram]

hi 

have a look at the attach file 

using interval match you can connect the two tables

i don't know from where you calculated the amounts in your question 

but the results in the files seems to be the right amounts for each period , you can recheck in your excel 

[Vegar]

Do I understand you correcty?

If you select dates associated to March 2020 then you want Sum(Sales) to be sum of all sales for M001 (even the sales that is not done in March 2020)?

If so, then why do you not want to present M003, hence it does also have an scheme that is related to March 2020? Please explain?

If my understanding is correct then @lironbaram  solution using IntervalMatch is a good start, but to solve you non-associative needs you will probably need to adjust your Sum (Sales) with some kind of set analysis.

Good luck!

[tripatiqv · Accepted solution]

the issue is resolved by creating composite key between scheme master table(statecode&Schemedate&MaterialCode)

and sales transaction master.

Thanks all for quick response