topic Re: Nearest value in QlikView

Nearest value

ecabanas — Tue, 20 Sep 2016 08:17:05 GMT

Hi guys,

I have a item price list, and I would like to find the nearest value from a date selected. For example:

If I select February, the result has to be: 0,88 if I select APril: 0,52

Many thank's for your help

Eduard

MK_QSL — Tue, 20 Sep 2016 08:24:11 GMT

Create something like below in script..

Data:

Load * Inline

[

Proveedor_Tarifa, ItemId, From, To, Tarifa, Tarifa_Id

PROV00283, 10357856, 01/02/2016, 29/02/2016, 0.88, 10357856-42401

PROV00283, 10357856, 01/03/2016, 31/12/2016, 0.52, 10357856-42430

];

Temp:

Load

From,

To,

Month(MonthStart(From, IterNo()-1)) as Month

Resident Data

While MonthStart(From, IterNo()-1) <= MonthStart(To);

Now you can use Month as Filter and =SUM(Tarifa) as expression.

ecabanas — Tue, 20 Sep 2016 08:32:42 GMT

Hi Manisch

The problem is not in the load scrip, this operation has to be done in a set analisys in a table

Many thank's

MK_QSL — Tue, 20 Sep 2016 08:34:43 GMT

Without any change in script, how you get April month here? Have you created master calendar?

ecabanas — Tue, 20 Sep 2016 08:37:17 GMT

Hi Manisch

Yes, see the example:

MK_QSL — Tue, 20 Sep 2016 08:39:14 GMT

So this Month field is created based on which field?

ecabanas — Tue, 20 Sep 2016 08:54:14 GMT

when you select the month in the upper band, the field shows the mothend value. Is automatic

sunny_talwar — Tue, 20 Sep 2016 10:32:31 GMT

I think you need to look into IntervalMatch‌ or use the logic from this link