Straight table filtering and expression problem

Tue, 25 Feb 2014 09:19:23 GMT

Hi all,

I am trying to filter a table with specific conditions but I have some problem with the expression.

The starting table is the one in FigureA.

I have an input box (Data1 variable) to filter on the Date column and I have the following dimensions: Code, Date, Type.

The expression is: Value.

What I want to achieve is to show the rows under a specific date, of Type A and with the Value related to the closest date for that Code.

This is the expression for the calculated dimension Date:

= if(len(Data1) = 0 , Date, if(num(Date) <= Num(Data1), Date,null()))
This is the expression for the calculated dimension Type:

= if(Type = 'A', Type, null())

I suppress the null values.

I obtain the table in FigureB.

Now the problem is to show on the column Value the value related to the closest date for each Code (Figure C).

I tried with the following expression:

aggr( max({$<TRDATE = {'=$(FilterAll)'}>} Value),Date), with FilterAll: =aggr( max(TOTAL {$<Date = {'<=$(=Data1)'}>} Date),Code,Date)

The strange behaviour is that if the date is correct it shows the value corresponding to the lower date for that code (Value 111 for Code 0000017) or a null value while it works when the closest day is not the one selected (Value 104 for Code 0000012)

How can I obtain the FigureC?

Thank you and best regards,

Eros

topic Straight table filtering and expression problem in QlikView

Straight table filtering and expression problem