topic Re: Variable dimensions in QlikView

Variable dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

This must be easy I hope: I've create a variable vCritDimension which content is:

=If(GetSelectedCount(Dimension)>0,CONCAT(DISTINCT Dimension, ', '))

Where Dimension is a result of:

LOAD * INLINE [

Dimension

'Cliente NIF'

'Articulo'

];

I have a list Dimension and use the selected values to show or hide dimensions with the next condition, and it works:

=GetSelectedCount(Dimension)>0 and SubStringCount( GetFieldSelections(Dimension), 'Cliente NIF')>=1

But when I use vCritDimension in an Aggr function it's not understood as dimensions... Do I miss something with $ or whatever??

Re: Variable dimensions

sunny_talwar — Thu, 26 Oct 2017 15:02:39 GMT

May be change your variable to this

=If(GetSelectedCount(Dimension)>0, Concat (DISTINCT '[' & Dimension & ']', ', '))

and use your variable within a dollar sign expansion

$(=vCritDimension) or $(=$(vCritDimension))

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 15:42:22 GMT

Sunny, it works but have a problem:

If I use one (of the two available) dimension, and click on the calculated dimension, it's filtered correctly, but if I use two dimensions, the clicking/filtering behaviour is not correct. This is the formula used in the calculated dimension:

=Aggr(

If(Sum({<Y = {"$(=[Año 1])"}, Origen = {"$(=[Origen C1])"}>}[Venta Neta])=0 and sum({<Y = {"$(=[Año 2])"}, Origen = {"$(=[Origen C2])"}>}[Venta Neta])<>0,'Ventas perdidas',

If(Sum({<Y = {"$(=[Año 2])"}, Origen = {"$(=[Origen C2])"}>}[Venta Neta])=0 and sum({<Y = {"$(=[Año 1])"}, Origen = {"$(=[Origen C1])"}>}[Venta Neta])<>0,'Ventas nuevas',

If(Sum({<Y = {"$(=[Año 1])"}, Origen = {"$(=[Origen C1])"}>}[Venta Neta])>=Sum({<Y = {"$(=[Año 2])"},Origen = {"$(=[Origen C2])"}>}[Venta Neta]), 'Incremento ventas',

If(Sum({<Y = {"$(=[Año 1])"}, Origen = {"$(=[Origen C1])"}>}[Venta Neta])< Sum({<Y = {"$(=[Año 2])"},Origen = {"$(=[Origen C2])"}>}[Venta Neta]), 'Disminución ventas',

)))), $(=vCritDimension))

It's only the clicking/filtering function, and I can live with it, but I don't understand it, showed information is correct, and I can expand and contract correctly... I just cannot follow the logic,when IO click some information is filtered, and other is not, but it seems that there's no logical explanation.

Re: Variable dimensions

sunny_talwar — Fri, 27 Oct 2017 15:45:55 GMT

the clicking/filtering behaviour is not correct.

What exactly are you clicking/filtering? In the chart itself? Would you be able to share a sample to demonstrate the issue?

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 15:47:12 GMT

Yes,. it is in the chart itself, on the output value in the calculated dimension column, I'll try to update a small example I have been using...

Re: Variable dimensions

sunny_talwar — Fri, 27 Oct 2017 15:52:38 GMT

Ya I can see why it would be a problem. Not sure if I have a solution for this problem.

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 16:00:49 GMT

Eh? I can't find the attach function?!?!?!

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 16:08:08 GMT

Here it is...

Having [Articulo] y [Cliente NIF] selected, when you click in Example a calculated dimension, it doesn't work propperly...

Re: Variable dimensions

sunny_talwar — Fri, 27 Oct 2017 16:21:02 GMT

Checking...

Re: Variable dimensions

sunny_talwar — Fri, 27 Oct 2017 16:29:24 GMT

Not sure how many dimensions are possible, but you might have to do something like what I did to make it work....

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 19:35:20 GMT

It drives me crazy... .It works in the attached .qvw but when I change it in mine .qvw, it doesn't... There are only three dimensions, but two to choose... I understand that you only have changed vCritDimension for:

=If(GetSelectedCount(Dimension)>0, '[' & Concat (DISTINCT Dimension, ' ') & ']')

Re: Variable dimensions

sunny_talwar — Fri, 27 Oct 2017 19:37:49 GMT

Also, created a new field in the script

LOAD Origen,

Articulo,

[Cliente NIF],

[Grupo Cliente],

Articulo&[Cliente NIF] as [Articulo Cliente NIF],

[Venta Neta]

FROM

(ooxml, embedded labels);

LOAD * INLINE [

Dimension

'Cliente NIF'

'Articulo'

];

Re: Variable dimensions

Anonymous — Fri, 27 Oct 2017 22:27:58 GMT

Perfect!!! Thanks a lot, Sunny!