Set analysis error

[pgalvezt]

Hi,

Im trying to filter some fields. have the next formula:

If(Count_desc='Sales Account',Sum({<subAccount={'L010''}>}Amount),

Sum(Amount))

+

If(cuenta_desc='Sales Account',Sum({<subAccount={'Blank'}>}Amount))

QlikView shows the expected result, But doesn't throw all the information. Like =Sum(Amount)

I attached an image as example.

Thanks!

[swuehl · Accepted solution]

If your second condition is not fulfilled, NULL will be returned as operand to the addition, which returns NULL.

So it's more safe to either write

=If(Count_desc='Sales Account',Sum({<subAccount={'L010''}>}Amount),

Sum(Amount))

+

If(cuenta_desc='Sales Account',Sum({<subAccount={'Blank'}>}Amount),0)

or

=rangesum(

If(Count_desc='Sales Account',Sum({<subAccount={'L010''}>}Amount),

Sum(Amount)),

If(cuenta_desc='Sales Account',Sum({<subAccount={'Blank'}>}Amount))

)

In addition, it's a common error to include the aggregation inside a condition, while you really want to include the condition in the aggregation, so if Count_desc is not a dimension, your expression will probably return an ambiguous answer in the conditional branch, thus failing your expectation.

[pgalvezt]

Thanks swuehl, just one more thing. I tried to show just one row.

=If(cuenta_group_desc='Total Costo de Ventas',0)

but I couldn´t 116.843.287 is the value that I want to show the rest doesn't care.

Can you help me with this?

[swuehl]

=If(cuenta_group_desc='Total Costo de Ventas',0)

This is always returning either 0 or NULL, won't it?

I think you should rethink about conditions used in an aggregation:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

Then maybe you come to something like

=sum( if(cuenta_group_desc = 'Total Costo de Ventas', Amount))

or using set analysis

=sum({<cuenta_group_desc = {'Total Costo de Ventas'}>} Amount)

[pgalvezt]

Hi, Finally I could view all values but for now its impossible to delete those rows. Look at the picture I have all correct values but I don´t wnat to see that I marked in red.

[swuehl]

Have you tried filtering your Cuenta dimension values, like

sum({<Cuenta -= {'Costo de Muestras','Costo de Garantias'}>} Amount)

[add more values to the field modifier list as needed]

[pgalvezt]

I can´t because I have values for each month. Let me explain you.

For example

for february I have

=If(Count_desc='Sales Account',Sum({<subAccount={'L010''}>}Amount2),

Sum(Amount))

+

If(cuenta_desc='Sales Account',Sum({<subAccount={'Blank'}>}Amount2),0)

For March

=If(Count_desc='Sales Account',Sum({<subAccount={'L010''}>}Amount3),

Sum(Amount))

+

If(cuenta_desc='Sales Account',Sum({<subAccount={'Blank'}>}Amount3),0)

and so on Amount 4 Amount 5.

So I think I can´t put Just Amount into my dimmension.

[pgalvezt]

Anyway thank you very much for your help I will keep going to try.