Using correct data selected by date

[#1]

Hi all,
i have a problem finding the correct formula to make that funcion:

That are the data i have for each invoice:

Field: "Customer" ; "date" ; "Sales" ; "Cost"

then i load an Inline Table like this:

LOAD * INLINE [
Period, Transport_Cost_Rate
01/01/2010, 0.01
01/01/2011, 0.02
];

Now for calculating the net margin i have to calculate the exact transport cost by this formula "Sales" * "Trasport_Cost_Rate" , but i can't do it because i can't select the correct rate related to the same year of the domument ("date")

Can you help me using the correct formula for Qlik ?

Thank you very much.

[johnw · Accepted solution]

So your periods are actually intended to be full years? I'd just link things up using the data model.

Rates:
LOAD
date(Year,'YYYY') as Year
,Transport_Cost_Rate
INLINE [
Year, Transport_Cost_Rate
01/01/2010, 0.01
01/01/2011, 0.02
];

Calendar:
LOAD
Date
,date(yearstart(Date),'YYYY') as Year
... other date-related fields ...
from whatever source of dates you're using for your calendar
;

Now the date from your invoice links to the date in the calendar, giving you the year, which links to the year in the rates table, which gives you your transport cost rate. QlikView does all of that association automatically, so your simple multiplication should now work.

[#3]

Thank you for the answer ,

you understand my request.

I will modify my load script to use the year link for the transport_Cost_Rate