Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

Try one of these

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

Or this

=Sum({$< ComponentID = {498}, MonthNr = {"$(=MaxString(MonthNr))"}>} Loonresultaat)
Highlighted
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

Highlighted
Contributor III
Contributor III

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!

 

Highlighted

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

Highlighted
Contributor III
Contributor III

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

Thanks again and nice evening!