Dynamic dimensional scope

Eman — Sat, 16 Nov 2024 19:14:05 GMT

Hi all,

I'm trying to create a report composer where the users can create pivot tables by their own choosing which dimensions and expressions show in the table.
Everything seems to work fine until I need to show the row relative values (%).

Absolute:

dim1	dim2	exp1	exp2	total
x	a	10	90	100
	b	30	30	60

Relative:

dim1	dim2	exp1	exp2	total
x	a	10%	90%	100%
	b	50%	50%	100%

= Count(DISTINCT my_value) / Count( TOTAL <dim1, dim2> DISTINCT my_value)

In this case i can easily define which are my dimensional scopes (dim1 and dim2) to obtain the correct relative value. But what about if user needs to add dim3? This change my formula to:

= Count(DISTINCT my_value) / Count( TOTAL <dim1, dim2, dim3> DISTINCT my_value)

Is it possible to do it dynamically?

I tried something like:

= Count(DISTINCT my_value) / Count( TOTAL <GetFieldSelections(dim_selected)> DISTINCT my_value)

or using variables to get the selected fields or, even, converting total to set analysis but without any success.

Can somebody help me please?

Thanks in advance,
Emanuele

Re: Dynamic dimensional scope

rubenmarin — Sun, 26 Jan 2020 09:56:43 GMT

Hi Emanuel, I think you attempt was close, can you try it using a $-expansion syntax?:

= Count(DISTINCT my_value) / Count( TOTAL <$(=GetFieldSelections(dim_selected))> DISTINCT my_value)

Or using concat:

= Count(DISTINCT my_value) / Count( TOTAL <$(=Concat(dim_selected,','))> DISTINCT my_value)

Re: Dynamic dimensional scope

Eman — Mon, 27 Jan 2020 11:54:41 GMT

Hi Ruben,

first solution works properly! Thank you very much!

topic Re: Dynamic dimensional scope in QlikView

Dynamic dimensional scope

Re: Dynamic dimensional scope

Re: Dynamic dimensional scope