Simple question... Max Value in set analysis

[intervigilium]

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.

[sunny_talwar]

Try one of these

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

Or this

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

[prieper]

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]

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 · Accepted solution]

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]

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

Thanks again and nice evening!