Dynamic Fields in a Pivot Table + GetObjectField() function

[alexquimu]

Hello,

I am working with a pivot table in Qlik Sense with dynamic dimensions. The dimensionality is not fixed and might be affected by selections on two different data islands. I have to be careful as hidden dimensions impact this problem, as GetObjectField() can return a blank result ('') if the dimension's condition is not fulfilled.

For my app, I need to compute percentages for each dimension by using the TOTAL qualifier with a different number of fields according to the dimensionality of the pivot table. The problem is users are allowed to change the order of the dimensions by moving the fields in the pivot table. For instance, they might put field2 before field1, and all other possible combinations.

Therefore, the order for the dimensions is really important as I compute the TOTAL according to the dimensionality of the table and the order of the fields. I use GetObjectField() along with variables to define the first dimension that is shown, vFirstDimension, defined as:

if(len(GetObjectDimension(0)) > 0, 0,
    if(len(GetObjectDimension(1)) > 0, 1,
        if(len(GetObjectDimension(2)) > 0, 2,
        ''
        )
    )
)

 Therefore, I can now use GetObjectField($(vFirstDimension)) to get the first dimension shown. For the second available dimension, I define vSecondDimension:

if(len(GetObjectDimension($(vFirstUsedDimension)+1))>0,$(vFirstUsedDimension)+1,
	if(len(GetObjectDimension($(vFirstUsedDimension)+2))>0,$(vFirstUsedDimension)+2,''))

This does work, but as you can imagine, there are plenty of nested if statements, and the final app is getting slow because of this.

Note: dimensions are shown according to the selections made on a data island; however, I can't simply concat the field as it won't respect the order when moving the field order in the pivot table itself.

Do you have any suggestions for resolving this performance problem?

Thanks in advance.

Greetings,
Alex

[marcus_sommer]

I think it's not mandatory necessary to adjust the column-order within the object else it could be done from the outside - maybe like the dimensions/expressions are chosen - respectively if everything is controlled from the outside nothing needs to be queried within the object.

[alexquimu]

Hi @marcus_sommer 

Thank you for your answer.

Are you suggesting we control the dimension order by, for instance, enabling the 'always one selected value' in the field and then duplicating the data islands? This would ensure we always control the dimension used in each data island, and therefore the order.

I had considered this solution. However, since a maximum of five dimensions might be selected, I don't really like the idea of duplicating five data islands. Is this indeed the approach you recommend?

Moreover, I am afraid some users would keep adjusting the column order within the pivot table 😞 

[marcus_sommer]

More or less, yes - maybe something like:

Dim: load pick(recno(), 'A', 'B', 'C') as Dim autogenerate 3;

for i = 1 to 3
   [Dim$(i)]: load Dim as [Dim$(i)] resident Dim;
next

drop tables Dim;

An alternatively may be to load everything in a single because island-table may contain more as single field, like:

DynDim: load * inline [Dim1, Dim2, Dim3
A, A, A
B, B, B
...];

and then using different alternate states for each dim-field.

Another way could be to use n variables with an appropriate defined value-list (in QV it's possible as well as to prevent the column-moving within the properties).