How to hide cross table columns with null/empty value

[Guest]

Hi All,

I am trying to hide my columns where it contains empty value. The problem is that that particular column uses an OVER Previous() function and the first column from the table will always be blank or return null value since 'no previous' column available for reference.

My expression as follow:

Sum([Total_USD]) Then SN([Value] - Sum([Value]) Over (Previous([Axis.Columns])),0) as [Changes $],

Sum([Total_USD]) Then SN(([Value] - Sum([Value]) OVER (Previous([Axis.Columns]))) / Sum([Value]) OVER (Previous([Axis.Columns])),0) as [Changes %]

I could only display the cell values with 0 but not hiding the whole columns. I try to look for possible solutions and the most similar answer I get from here isHow to Hide a blank column in Crosstab | TIBCO Community. However, I would like to hide the column and not display 0 or any other values.

Please help! I have attached the dxp. file for your reference as well.

Thanks.

[Gaia Paolini]

can you give some details about how your cross table is structured and ideally a screenshot or a small reproducible example See these guidelines for helphttps://community.spotfire.com/wiki/quick-tips-asking-community-questions

[Guest]

Hi,

 

Due to confidential data, I will use the example from 2016 post.

 

for example, I have written an expression like this:

 

Sum([iB Count]), Sum([Material Cost]), Sum([LC]), Sum([Total Cost]), (Sum([Total Cost]) - Sum([Total Cost]) OVER (Previous([Axis.Columns]))) / Sum([Total Cost]) OVER (Previous([Axis.Columns])) as [Variance]

 

As the [Variance] column has no "previous" column to refer, the column show empty value, and I wish to hide the whole column instead of using SN() to change it to 0. How can i actually hide the column for Variance Q32016

 

Thank you.

[Gaia Paolini]

thanks for the example. I am seeing an empty column for Variance Q3205, not Q3206, is that the one you mean I cannot see how it is possible to hide the Variance only for Q3205 and leave the one for Q3206. If you want to hide the Variance column altogether when either Q3205 or Q3206 are empty, there might be a way (but I cannot think of one yet)

[Guest]

Yes, it should be Q3205, sorry for the typo. No worries, I will keep this question open.

 

Thanks for your help!