Selective Dimensions in Pivot Table

[mansoorsheraz]

Hi Everyone,

My data is like this

I want to display the pivot table in a way that the Rows are not duplicated for the locations with '-' in them. Currently it looks like this:

But I want that the + sign should not appear before the 19 Div 1 here as the location4 and location5 are '-' for it. I have tried using if condition and also the "Show column if" but they are not working. Any help is appreciated.

Regards.

[rubenmarin]

Hi, dimensionality() returns the dimension level of the cell:https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter...

As you tested, the modification is not neccessary, I did it to test the behaviour of removing null values, I also prefer to not load values that I don't wan't the user to select.

And about the moving dimenion you can try with GetObjectDimension, in an expression like:

If((Dimensionality()=1 and not IsNull([$(=GetObjectDimension(0))])) or (Dimensionality()=2 and not IsNull([$(=GetObjectDimension(1))])) or (Dimensionality()=3 and not IsNull([$(=GetObjectDimension(2))])) or (Dimensionality()=4 and not IsNull([$(=GetObjectDimension(3))]))
  ,Sum([Active Employee])
)

Using Len instead of IsNull if you load the '-' as values.

[mansoorsheraz]

Hi,

Thanks again for reaching out to me. I will certainly check this out. One more question, is there way we can handle the "+" button there so that it should appear when there are no more indented rows of data in the Pivot Table?

Regards.

[rubenmarin]

Hi, I don't think so, to do that you'll need to edit the standard object to make your own extension with that behaviour.

[mansoorsheraz]

Hi,

This works fine till now but when I tried to add in "Totals" it doesn't work properly. The totals are only present in the first dimension and they don't work with the dimensionality checked measures. Is there any way we can have the totals in there?

Regards.

[rubenmarin]

Hi, totals has dimensionality()=0 just modify the expression to:

If((Dimensionality()=0) or (Dimensionality()=1...

[mansoorsheraz]

Hi @rubenmarin,

Thank you so much for your help. This worked. I did the mistake of doing all my working in the else portion of that "IF Condition" that wasn't working. Though I managed to get the total but it always disturbed the other arrangement that we did for hiding of rows. Thanks once again. You made me learn a few new things and also this has become a concrete thread for some Pivot table related issues.  You are a Genius!

Thanks.