analisis set expresion its only works when i filter by dimension

[#1]

This expresion only works when i filter by dimension like [Cuenta Contable] or [Codigo del area de actividad]

sum({$ <[Tipo de informe] = {5},[Cuenta contable de presupuesto] = {0}, [Año] = {$(=Año)}, [Código del area de actividad] = {$(=[Filtro area de actividad])}>}

[Importe a credito de la transacción] + [Importe a debito de la transacción])

The fields [Tipo de informe], [Cuenta contable de presupuesto], [Filtro area de actividad] are in Agrupacion de cuentas table.

The field [Año] is in Master Calendar table.

The fields [Código del area de actividad],[Importe a credito de la transacción],[Importe a debito de la transacción] are in Transacciones cliente table.

This expression is in pivot table who has the dimensions [Nivel 1],[Nivel 2],[Nivel 3],[Nivel 4],[Cuenta contable].

i dont knon why???????.

thanks a lot.

[#2]

I'll be honest I really don't really understand the example, but just based on the question you might need to use aggr. I know in some things that I've done, say if there are two invoice payments made on the same date that go towards different invoices, it isn't going to work unless I either show more dimensions or use aggr. Again, I'm really not sure what your example is saying, so I'm not sure.

[Miguel_Angel_Baeyens]

Correct me if I'm wrong, but it seems you are using another field to filter, which is [Filtro area de Actividad], which value will be passed on the set analysis to the [Código del area de actividad].

So your expression will only work when [Filtro area de actividad] has one value, and my guess is that that happens when you select any of [Cuenta Contable] or, of course, [Código del area de actividad]. (¿In the first case, because an explicit selection in [Cuenta Contable] will mean an implicit selection of [Código del area de actividad]?)

If that's true, then give a try to the following:

sum({$ < [Tipo de informe] = {5}, [Cuenta contable de presupuesto] = {0}, [Año] = {$(=Año)}, [Código del area de actividad] = P([Filtro area de actividad]) >}[Importe a credito de la transacción] + [Importe a debito de la transacción])

The P() function will return all possible values for the selection (if any) done in the field used as a parameter. So if no field is filtered, it will take all possible values in [Filtro area de actividad] and will apply them to [Código del area de actividad].

Hope this helps

[#4]

Thanks a lot for your's replies,

You are right, the value of field [Filtro area de Actividad] is using to filter in the analysis set to the [Código del area de actividad] field, but this field values are static and are loaded from database, is invisible for user. for example:

[Filtro area de Actividad] values:

'Pisos'

'Pisos','Comida','Bebida'

'Comida'

I try your pourpose and it dosen´t works.

[Miguel_Angel_Baeyens]

Hello,

If the user is not going to select directly over [Filtro area de Actividad], you better don't use it in the set analysis, and likewise with the year:

um({$ < [Tipo de informe] = {5}, [Cuenta contable de presupuesto] = {0} >}[Importe a credito de la transacción] + [Importe a debito de la transacción])

Hope this helps

[#6]

Hello,

The problem is that the table are not linked, and can not be linked because in many cases the value of [Filtro area de Actividad] is empty and it's mean that you have to use all [Area de actividad] possible values. I´m trying to use an If but the filter structure repeat for ten fields, [Area de negocio] --> [Filtro area de negocio], [Proyecto] --> [Filtro de proyecto], [Unidad de negocio] --> [Filtro de unidad de negocio], etc...

Thanks a lot.

[Miguel_Angel_Baeyens]

Hello,

Then create a new text object and set the following to check if that fit your needs:

=chr(39) & Concat(distinct [Filtro area de negocio], chr(39) & ',' & chr(39)) & chr(39)

It should return a list of all possible (or selected if any) values in the field [Filtro area de negocio], quoted and separated by comma. Note that in QlikView field names and values are case sensitive, so field [Filtro Area] and [Filtro area] are different.

If that returns the right value for when values are selected and not selected, then you can use it in set analysis like

sum({$ < [Tipo de informe] = {5}, [Cuenta contable de presupuesto] = {0}, [Código del area de actividad] = {$(=chr(39) & Concat(distinct [Filtro area de Actividad], chr(39) & ',' & chr(39)) & chr(39))} >}[Importe a credito de la transacción] + [Importe a debito de la transacción])

Hope this helps

[#8]

One million of thank, thanks a lot.

You are the best.