topic Re: max value in set analisys in QlikView

max value in set analisys

gidon500 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 14:24:16 GMT

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

Re: max value in set analisys

gidon500 — Mon, 17 Dec 2018 14:36:10 GMT

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

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 14:52:28 GMT

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

Re: max value in set analisys

gidon500 — Mon, 17 Dec 2018 15:03:53 GMT

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

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 15:05:23 GMT

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

Re: max value in set analisys

gidon500 — Mon, 17 Dec 2018 15:17:43 GMT

yes on 12/12/18 should be 9

on 08/01/18 should be 2

thanks

gidon

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 15:22:12 GMT

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)

Re: max value in set analisys

gidon500 — Mon, 17 Dec 2018 15:34:10 GMT

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

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 15:43:29 GMT

Try this

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

Re: max value in set analisys

gidon500 — Mon, 17 Dec 2018 16:02:14 GMT

Hi Sunny

it seems to work

I will put it in my document and come back to you

thank you very much

gidon

Re: max value in set analisys

sunny_talwar — Mon, 17 Dec 2018 16:05:23 GMT

Sure thing 🙂

Re: max value in set analisys

gidon500 — Tue, 18 Dec 2018 06:35:48 GMT

Hi Sunny

enclosed is qvw file
your expression is working fine , it is located in column four
( i do not understand why i get ' error in expression ' )
it took this expression and put it in culumn Five
which is copy of column third but replace the Field SOSSTAT WITH
the expression in column four
it does not work

Can it Work ?
thanks you very much for your help
gidon

Re: max value in set analisys

sunny_talwar — Tue, 18 Dec 2018 12:05:59 GMT

Let's take a step back and work our way through it. What exactly are you trying to do here?

Re: max value in set analisys

gidon500 — Tue, 18 Dec 2018 12:40:55 GMT

Hi Sunny

The purpose of the documents it count number of students (Codes)

at the given time ( the variable vMaxsunday )

Count the students according to the latest known SOSSTAT , Registered is first , Accepeted is second and Started third

SOSSTAT can be changed any day , So I need to know the Latest SOSSTAT according to the vMaxsunday

in order to get the right number for the requested vMaxsunday date .

I hope I explained my needs .

thanks for your help

gidon

Re: max value in set analisys

sunny_talwar — Tue, 18 Dec 2018 12:47:06 GMT

This make sense, but what exactly are you trying to do with Third4 expression? You found the latest SOSSAT and not you want to count it against Code? What is the output you expect? Can you share this in an Excel file?

Re: max value in set analisys

gidon500 — Tue, 18 Dec 2018 13:01:06 GMT

in the second and the third I am check for SOSSTAT 1 and 2

the problem is that the SOSSTAT is not correct

I need the SOSSTAT according to the value I get using the expression you gave me .

so what I did in third4 ,

I replaced the SOSSTAT with the expression

gidon

Re: max value in set analisys

gidon500 — Tue, 18 Dec 2018 14:24:31 GMT

Hi Sunny

It is very difficult to explain the needs

was I clear ?

thanks

gidon

Re: max value in set analisys

sunny_talwar — Tue, 18 Dec 2018 14:31:57 GMT

I understand that the requirement might not be very straight forward, but to make it simple... I suggest you to provide the expected output in an Excel file based on the data you have provided in Document2.qvw file. My guess is that you don't know the right expression, but you at least know what the right numbers need to look like, right? All I am looking for is the right number you want to see and if possible the logic behind one of those numbers.

Re: max value in set analisys

gidon500 — Tue, 18 Dec 2018 15:21:08 GMT

I will make an excel file and send it over

thank you very much

gidon