Date greather than less than fields in set analysis

[olramirez]

Hello,

I have a price table with 4 columns:

Product          Start_Date          Final_Date         Price

P1                     2016-01-01          2017-12-31          20              

P1                    2018-01-01          2019-12-31           25

P2                    2016-01-01          2017-12-31           32           

P2                    2018-01-01          2019-12-31           35

The report sales should be, example:

%Date                   Product          QTY          Price

2018-05-01               P1                2                   25

2019-01-03               P2                4                    35

But the Column Price is null when i make this Set Analysis:

Max({$<Year=,Month=,%Date={'>=Start_Date<=Final_Date'}>}Price)

I tried, this one, with variables  vDateSt=Start_Date and vDateFin=Final_Date

max({$<Año=,Mes=,%FECHA={'>=$(=vDateSt)<=$(=vDateFin)'}>}Price)

But this doesn't work anyway

Could someone help me please.

[jonathandienst · Accepted solution]

You need an interval match to fetch the correct prices:

Left Join(Fact)
IntervalMatch(Start_Date, Final_Date, Product)
LOAD Start_Date, Final_Date, Product
Resident PriceTable;

//optional ----
Left Join(Fact)
LOAD *
Resident PriceTable;

DROP Table PriceTable;

Now use the Price field.

These second join and drop are to simplify the resulting data model, but are not strictly necessary.

[olramirez]

Hello Jonty,

i have tried your solution, but this doesn't work.

This is an extract from the model, could you tell me what am i doing wrong?

[olramirez · Accepted solution]

Sorry, i got it, i know what is wrong.

This is the answer, thanks Jontydkpi