topic Help with set analisys in QlikView

Help with set analisys

rcandeo — Thu, 01 Oct 2009 01:09:47 GMT

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.

Help with set analisys

Thu, 01 Oct 2009 01:37:25 GMT

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.

Help with set analisys

rcandeo — Thu, 01 Oct 2009 02:03:45 GMT

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))

Help with set analisys

Thu, 01 Oct 2009 02:30:19 GMT

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 ).

Help with set analisys

rcandeo — Thu, 01 Oct 2009 02:51:04 GMT

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?

Help with set analisys

Thu, 01 Oct 2009 02:59:54 GMT

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.

Help with set analisys

rcandeo — Thu, 01 Oct 2009 03:35:24 GMT

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.