Help with set analisys

[rcandeo]

I have this application that in the table Resumo com Dimensões do Item, the field Saldo M3 has the expression bellow:

Sum({$<Mes={'$(=Max(Mes))'},Ano={'$(=Max(Ano))'}>} aggr(avg(Saldo_M3),Item_Curto_ITM,Filial_Fabrica_MCU,Local_LOCN,Lote_LOTN))

If my user selects more than one month, I wanna show the value of the last month selected and it is not working neither with a single month selected.

[#2]

Something is wrong with the page here, so I'm doing a quick reply. Don't know if it's the length of your expression or if they're messing with the site again.

I think your problem is that Max(Mes) is a single-digit number (6) when it is less than 10. In your data, it must be a double-digit (06). It seems to work if the max month selected is 10, 11 or 12.

You can use Num(Max(Mes), '00') to force a two-digit number.

[rcandeo]

Is the expression correct? It gaves me an error in expression:

Sum({$<Mes={'$(=Num(Max(Mes),'00')))'},Ano={'$(=Num(Max(Ano),'00')))'}>} aggr(avg(Saldo_M3),Item_Curto_ITM,Filial_Fabrica_MCU,Local_LOCN,Lote_LOTN))

[#4]

Sorry, you can't put quotes around the 00 in the Num function. Since the dollar sign expansion is already in quotes, putting quotes inside breaks it. Thankfully, it seems to work fine without the quotes.

Sum({$<Mes={'$(=Num(Max(Mes),00))'},Ano={'$(=Max(Ano))'}>}
aggr(avg(Saldo_M3),Item_Curto_ITM,Filial_Fabrica_MCU,Local_LOCN,Lote_LOTN))

I don't think you need a Num function on Ano. That's Year, right? That will always be a four-digit, since it will never start with a 0 (I hope ).

[rcandeo]

Sorry, but didn´t work. See the example attached with the expression modified. Ano is Year, you are right.

If I select month 9, it works (has just one record).

If I select month that has more than one record it gaves me 0 as result (I took out the avg from the expression, since I need the total value)

If I select month 6 and 9, it gaves me 0 too. As I use the expression max, shouldn't the result be as I select month 9?

[#6]

I think it is a problem with your Aggr function and not your Set Analysis.

One of the best tricks for testing Set Analysis is to put it into an expression, but don't give that expression a label. Then the full Set Analysis expression (with dollar-sign expansions evaluated) will be the label.

I tested the expression I gave you while 9 was selected for Mes and where 5 & 9 were selected and the Set Analysis stays exactly the same. The value of that expression does change though and it seems to be that there is a blank UM record.

I tried adding Unidade_Medida_TRUM into the Aggr function, but that didn't fix it. It's kind of hard to keep up with that Aggr function, so I don't know what else I could suggest.

[rcandeo]

I think you helped me to find a solution, and it is quite simple. I don't know why I did so many complicated:

max({$<Mes={'$(=Num(Max(Mes),00))'},Ano={'$(=Max(Ano))'}>} Saldo_M3)

Since I have only one value as Saldo_M3 in a month and some records have null value, if I use max, it gaves the correct value.

Thank you for your help.