Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

Re: Date greather than less than fields in set analysis

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

Highlighted
Contributor III
Contributor III

Re: Date greather than less than fields in set analysis

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

This is the answer, thanks Jontydkpi

View solution in original post

3 Replies
Highlighted
MVP
MVP

Re: Date greather than less than fields in set analysis

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

Highlighted
Contributor III
Contributor III

Re: Date greather than less than fields in set analysis

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?

Highlighted
Contributor III
Contributor III

Re: Date greather than less than fields in set analysis

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

This is the answer, thanks Jontydkpi

View solution in original post