Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

analisis set expresion its only works when i filter by dimension

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.

7 Replies
Not applicable

analisis set expresion its only works when i filter by dimension

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.

analisis set expresion its only works when i filter by dimension

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

Highlighted
Not applicable

analisis set expresion its only works when i filter by dimension

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.

analisis set expresion its only works when i filter by dimension

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

Not applicable

analisis set expresion its only works when i filter by dimension

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.

analisis set expresion its only works when i filter by dimension

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

Not applicable

analisis set expresion its only works when i filter by dimension

One million of thank, thanks a lot.

You are the best.