Expression with information that does not relate to all the columns of a table

[#1]

Hello.

I have a dinamic table. I need to add an expression on it, but the information used by this expression does not relate to all the columns of the table and, as a consequence, Qlikview creates another line with empty values for the columns that de info from this expression does not have.

As the print above shows, expression "Preço Venda Varejo" does not have information for columns Previsão, Pedido/OP Quad and Pedido/OP.

What I'm trying to achieve is represented by the image below

Is there a way to make Qlikview ignore the impossibility to relate the expression to those 3 first columns ?

This is my current expression - SUM(PRODUTO_VALOR_ORIGINAL_VENDA * VENDA_QUANTIDADE_BRUTO) / SUM(VENDA_QUANTIDADE_BRUTO)

Thank you.

[swuehl]

You may use the TOTAL qualifier (with a field list) in your aggregations to disregard some of your dimensions.

So you can show your expression values also in rows where they are currently not shown.

The Aggregation Scope

[#3]

Hi Swuehl, thanks for the reply.

I tried experimenting with the TOTAL qualifier, but it resulted in another problem... the dynamic table is now showing lines ignoring the current filters.

My current expression is SUM(TOTAL <PRODUTO_REF_BASICA> PRODUTO_VALOR_ORIGINAL_VENDA * VENDA_QUANTIDADE_BRUTO) / SUM(TOTAL <PRODUTO_REF_BASICA> VENDA_QUANTIDADE_BRUTO).

So now the expression considers only PRODUTO_REF_BASICA, the dimension that is represented on the REF BASICA column on the print, in order to calculate, but that is being used by the table as a whole that is ignoring the date filter...

The correct result would be 19,99 copied in every single line that has value for column Previsão. All lines showing empty data on Previsão column should not appear.

Do you think there is a way to not consider only certain dimensions to calculate data, but without showing lines that don't match the filters ?

I'm still reading your link and further topics, experimenting variations.

Thank you again.

[Anonymous]

Maybe adding this?

SUM( { $ < Previsão -= {''} > } TOTAL <PRODUTO_REF_BASICA> PRODUTO_VALOR_ORIGINAL_VENDA * VENDA_QUANTIDADE_BRUTO) / SUM(TOTAL <PRODUTO_REF_BASICA> VENDA_QUANTIDADE_BRUTO).

[sfatoux72]

Hi,

Did you try to simply use the option "Suppress When Value Is Null" on your three first dimensions ?

[#6]

Hi Sébastien.

Yes, I tried that and it resulted in an invalid value(-) for each line. This happens because this option leads both parts of the expression to zero as a result. Since I have a division, the result is - (invalid).

[#7]

Hello.

I tried your suggestion as well... it did not evaluate to a valid result.

[Anonymous]

Would you be able to share with us a little sample, Caio?