topic Re: Re: Set analysis error in QlikView

Set analysis error

pgalvezt — Mon, 18 Nov 2013 16:13:52 GMT

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!

Re: Set analysis error

swuehl — Mon, 18 Nov 2013 16:19:41 GMT

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)

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

Re: Re: Set analysis error

pgalvezt — Mon, 18 Nov 2013 18:04:23 GMT

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?

Re: Set analysis error

swuehl — Mon, 18 Nov 2013 18:11:06 GMT

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

Re: Re: Set analysis error

pgalvezt — Mon, 18 Nov 2013 18:41:34 GMT

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.

Re: Set analysis error

swuehl — Mon, 18 Nov 2013 18:47:53 GMT

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]

Re: Re: Set analysis error

pgalvezt — Mon, 18 Nov 2013 19:00:44 GMT

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.

Re: Re: Set analysis error

pgalvezt — Mon, 18 Nov 2013 19:09:31 GMT

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