topic Logic Help Ratio in QlikView

Logic Help Ratio

abhaysingh — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

i Have data in attached excel like below format

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 — Thu, 14 Apr 2016 09:20:09 GMT

To do this you need to use interval match: IntervalMatch

sunny_talwar — Thu, 14 Apr 2016 09:25:22 GMT

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: