topic Re: Always one specific dimension first in pivot table in App Development

Always one specific dimension first in pivot table

henrikalmen — Tue, 07 Mar 2023 09:50:27 GMT

I have a pivot table in Qlik Sense, and there are multiple dimensions for rows and columns that the user is allowed to move around to explore the data. The first row dimension should always stay the same - it must not be moved. If the user moves it I want to use the calculation condition for the pivot table to display an error message so that the user is forced to move that field back to its obligatory location.

Apparently it is not possible to use GetObjectDimension() as calculation condition, it just fails with an error (even though it is actually possible to use that expression in the "Displayed message" field for when the calculation condition fails).

Can I achieve what I want some other way? I'm out of ideas. (And why is GetObjectDimension() not usable in the calculation condition?)

Re: Always one specific dimension first in pivot table

henrikalmen — Fri, 10 Mar 2023 13:23:18 GMT

An alternative "solution" (although it's not a solution) I've come up with is to create a new dimension with the label "WARNING! Move dimension X back" and the measure is something like ='You must have dimension X as first dimension', and I set the calculation condition to =GetObjectDimension(0)<>'[thedimensionthatmustbefirst]' so that this dimension is only visible when the problem occurs. It's not what I want and the user might miss it, but it's better than nothing.

Better ideas are welcome! 🙂

Re: Always one specific dimension first in pivot table

rpennacchi — Sun, 12 Mar 2023 14:20:13 GMT

Hello, Henrikalmen,

Why don't you write a condition on your measure like this:

if(GetObjectField(0) = 'FIRST_FIELD_NAME',
SUM(VALUE) // YOUR EXPRESSION
,
'The first column must to be "NAME OF THE FIELD"'
)

So when the first column is not the column you want it will display that message.

To make it clearer for the user you can use this, to just show the error message just on the first row:

if(GetObjectField(0) = 'FIRST_FIELD_NAME',
SUM(VALUE) // YOUR EXPRESSION
,
if(rowno() = 1,
'The first column must to be "NAME OF THE FIELD"'
,
''
)
)

Re: Always one specific dimension first in pivot table

henrikalmen — Sun, 12 Mar 2023 20:16:32 GMT

But of course! Why did I not think of that! 🙂 Thanks! And great idea with the error message in just one place. (I'm also adding columnno()=0 to that message criteria).