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
