Logic Help Ratio

[abhaysingh]

Hi Guys,

i Have data in attached excel like below format

From Date	ToDate	Ratio
01.01.2016	10.01.2016	0.1
11.01.2016	15.01.2016	0.2
16.01.2016	31.01.2016	0.3

You can see in this, From Data and To Date, with in some  date range Ratio is different.

i have txn Data in which i have continuous date with their sales , now let say i have 1 month total sales,

now what i want is as per the given From and To Range the sale will come in given ratio.

e.g.

let say from 01.01.2016 to 10.01.2016, the total sale is 100 than output should come like .1 % of 100.

From 11.01.2016 to 15.01.2016, the total sale shud be 0.2 % of that range sale , say 50

Attaching  excel pls have a look for sample data.

Thanks

[sunny_talwar]

To do this you need to use interval match: IntervalMatch

[sunny_talwar · Accepted solution]

May be this?

Script:

Table:

LOAD Date#([From Date], 'DD.MM.YYYY') as FromDate,

    Date#(ToDate, 'DD.MM.YYYY') as ToDate,

    Ratio

FROM

[Ratio Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD Date,

    Value

FROM

[Ratio Test.xlsx]

(ooxml, embedded labels, table is Sheet2);

IntervalMatch:

IntervalMatch(Date)

LOAD FromDate,

  ToDate

Resident Table;

Data Model: