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]

If, you can try unchecking the option to show null values on the dimensions.

Another option could be to check the values beforre calcualtion the expression, like: If(Not IsNull(DimensionField), YourExpression)

And uncheck the option to include zero values in add-ons... note that this will remove all rows with zeros or nulls on all columns, so it won't work if you need to show rows with all zeros.

[mansoorsheraz]

Thanks. I tried with nulls already but that didnt work. I tried with length function too on the dimension, but what it does is that once the plus sign is clicked, it completely removes the "Divisional 1 Residential Sales" block or group.  This hyphen is in the data its not a null.

[mansoorsheraz]

Update on this issue however its still not resolved.
I tried to use a calculated dimension but when I suppress the null values, it completely removes the Div1 block as shown in the screenshots:

As the null is suppressed the top level completely disappears

This looked a simple task but it has gone to be a difficult one. I am, not finding any solution to it. Help required.

Regards.

[mansoorsheraz]

Looking for help on this issue dear community members.

[rubenmarin]

Hi, can you upload a sample with some dummy data to check?

[mansoorsheraz]

Thank you so much. I have attached the QVF file for you to review. There are two sheets in it, one with simple dimensions and the second one where I tried to exclude the ones that are having '-' in them as I dont want them.  Thanks once again for all the help.

[rubenmarin · Accepted solution]

Hi, I made it checking dimensionality and dimension. I don't like it because if the user changes the order of the columns it would be a mess... but if don't, it might work.

[mansoorsheraz]

Hi,

Thanks for getting back to me with the solution. You are genius for sure. It looks very much like it but I have to first embed it in the main app to see how it works. A few questions here:

1) Can this be done without the load script changes that you made i.e. adding an if condition?
2) I will check on dimensionality , its a new concept for me. Can you explain what it does?

I will check for the above two points myself as well but needed an expert opinion on this. Thanks once again. It looks like it. I will embed it in may main app and will accept this as a solution.  Highly appreciate this.

Thanks and Kind Regards.

[mansoorsheraz]

Hi,

Just an update that I was able to do it without the load level changes that you did. I checked for the length of the dimension instead of being not null. It worked as in my scenario I may not have any dimension with a single character value only.

Also why does it gets disturbed when the column order is disturbed? I even tried to code the if condition in the same arrangement but it didnt work out. What can be the issue? Can there be a better solution to this?

Thanks and Regards.