Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
olramirez
Contributor III
Contributor III

Date greather than less than fields in set analysis

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.

 

 

 

 

 

Labels (1)
2 Solutions

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

olramirez
Contributor III
Contributor III
Author

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

This is the answer, thanks Jontydkpi

View solution in original post

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
olramirez
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

This is the answer, thanks Jontydkpi