topic Re: Date greather than less than fields in set analysis in QlikView

Date greather than less than fields in set analysis

olramirez — Sat, 16 Nov 2024 03:19:55 GMT

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.

Re: Date greather than less than fields in set analysis

jonathandienst — Wed, 12 Jun 2019 05:58:16 GMT

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.

Re: Date greather than less than fields in set analysis

olramirez — Thu, 13 Jun 2019 01:43:45 GMT

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?

Re: Date greather than less than fields in set analysis

olramirez — Thu, 13 Jun 2019 17:57:00 GMT

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

This is the answer, thanks Jontydkpi