max value in set analisys

[gidon500]

Hi Guy 

I have an issue spent hours to solve could not do it 

Enclosed Qlik  Document.qvw
vMaxSunday is a date variable  - to set the report Date
in the table we have SOSSTAT for each code and a Date
I need to get the max date  SOSSTAT  in the object 
according to the date  set by the variable vMaxSunday
now i get last SOSSTAT set to each Code

How can I dot it
thanks
gidon

[sunny_talwar]

Which chart are you looking to get this in? Also, you only want to see a single row based on the variable vMaxSunday?

[gidon500]

Hi sunny 

I am looking for the chart  without the dates

enclose is a pdf to show 

I need to keep all rows  , only in the object I need

my problem is that I need the last  SOSSTAT  accoding vMaxSunday 

thanks appiricate your help 

gidon

[sunny_talwar]

So, in your chart, you want to see the Max(SOSSTAT) based on the vMaxSunday variable? May be this
Max({<Date = {"$(=Date($(=vMaxSunday)))"}>} SOSSTAT)

[gidon500]

Hi 

I want to see the SOSSTAT , 

For the max day close to the vMaxSunday , 

it is not the max value of the SOSSTAT , it is the SOSSTAT value  of the date 

thanks

gidon

[sunny_talwar]

I am not sure I am able to understand... so when you have vMaxSunday = 12/12/2018... you want to see 9, right?

[gidon500]

Hi 

yes on  12/12/18  should be 9 

on 08/01/18 should be  2 

thanks 

gidon

[sunny_talwar]

So, this is what you want, right?

When 12/12/2018 is selected... Third should show 9

When 08/01/2018 is selected... you want to see 2

I used the following expression for Third column

Max({<Date = {"$(=Date($(=vMaxSunday)))"}>} SOSSTAT)

[gidon500]

Hi

that is correct ,

the problem is when I select a date the is not on the list 

I do not get any result   . 

if I select for example the date 01/03/18 I should get  2  

as the date 08/01/18 is the closest date  with the value  2 

is it possible ?

thanks

gidon

[sunny_talwar]

Try this

FirstSortedValue(DISTINCT {<Date = {"<=$(=Date($(=vMaxSunday)))"}>} SOSSTAT, -(Date + (SOSSTAT/1E10)))