Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
intervigilium
Contributor III
Contributor III

Simple question... Max Value in set analysis

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.

1 Solution

Accepted Solutions
sunny_talwar

May be try this in that case

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

View solution in original post

5 Replies
sunny_talwar

Try one of these

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

Or this

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

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

intervigilium
Contributor III
Contributor III
Author

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!

 

sunny_talwar

May be try this in that case

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

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

Thanks again and nice evening!