topic Re: Simple question... Max Value in set analysis in QlikView

Simple question... Max Value in set analysis

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

Hi All,

Have a simple database with payroll information. For each month the value of the payment.

What is working:

textbox with : =max(MonthNr)

This gives back the number 08

Further in a straight tabel the following expression:

=sum( {$ < ComponentID={498}, MonthNr={08}> } Loonresultaat)

Also this is working......

BUT i can't get it working in 1 statement:

=sum( {$ < ComponentID={498}, MonthNr={'=max(MonthNr)'}> } Loonresultaat)

Tried a lot way of different writings (with = '' $ etc), but dont get it working.

Also made a variable vMaxMonth=max(MonthNr) and then use it in the expression is not helping.

Seems such a simple problem...... am overlooking something.

Re: Simple question... Max Value in set analysis

sunny_talwar — Fri, 23 Nov 2018 15:24:56 GMT

Try one of these

=Sum({$< ComponentID = {498}, MonthNr = {"$(=Max(MonthNr))"}>} Loonresultaat)

Or this

=Sum({$< ComponentID = {498}, MonthNr = {"$(=MaxString(MonthNr))"}>} Loonresultaat)

Re: Simple question... Max Value in set analysis

prieper — Fri, 23 Nov 2018 15:26:55 GMT

MonthNr "08" already contains a formatting.
The MAX() within the SetAnalysis would work only on numeric data.

Suggest to have a field MonthNr with the plain numeric value ("8"). Then formulas should work.

HTH Peter

Re: Simple question... Max Value in set analysis

intervigilium — Fri, 23 Nov 2018 15:45:02 GMT

Dear Stalwar,

Both expressions are working! I have to find out the difference between single and double qoutes ' versus ". But i will google that 🙂 Thanks for the solutions

I didn't use the double qoutes before.

Only one more question... the last month-number is different for each row. How do i add that to the expression?

I added a screenshot. Now it takes '9' as max month in the total table, but the max value is different voor each employee.

Thanks again in advance!

Re: Simple question... Max Value in set analysis

sunny_talwar — Fri, 23 Nov 2018 15:49:12 GMT

May be try this in that case

=FirstSortedValue({$<ComponentID = {498}>} Aggr(Sum({$<ComponentID = {498}>} Loonresultaat), MonthNr, Employee), -Aggr(Only({$<ComponentID = {498}>}MonthNr), MonthNr, Employee))

Re: Simple question... Max Value in set analysis

intervigilium — Fri, 23 Nov 2018 15:56:20 GMT

Really great, that is working! Would never got that working without help!

Thanks again and nice evening!