Solved: Re: Hide Measure Column Pivot Table - Qlik Community

tumrong · ‎2021-09-08

Hello, I am new to Qlik Sense. Can anyone please help me with this question. I am trying to hide column in the pivot table in Qlik Sense. Please see the picture. They are the columns in the circle I try to hide. Here is the command I use to create data in green column:

Sum(unit)/Before(Sum({<[Period]>}unit)) - 1

Is there any way to suppress columns in the circle. These columns are the percentage change created from the above command. I need the current period to show and hide the prior period columns. Thank you for the help.

rbartley · ‎2021-09-09

Unfortunately, there is no way to divide a cell and I do not believe that you can apply bold formatting either (at least not in the version I am using), so the best you can do is colour the whole measure as follows:

Add this expression to the text color property of the measure (again, changing the field names as appropriate):

if(Not(IsNull(Before(Sum({<[Period]>}[Shopper Total Spend])) - 1)),if(Sum([Shopper Total Spend])/Before(Sum({<[Period]>}[Shopper Total Spend])) - 1<0,red(),green()))

You can use the same approach to change the background.

View solution in original post

rbartley · ‎2021-09-08

Use the "Show column if" property of the measures that you want to hide and set it to 1=2 (or any other expression that will always evaluate to false).

tumrong · ‎2021-09-08

Thank you for your help. I have tried this approach. If I do this, the green column does not show neither. The green column has to present to the user. Do you have other method? Thank you.

rbartley · ‎2021-09-08

In which case, please upload your app (qvf) so that I can take a look, otherwise it will be difficult to help.

In the meantime, you could try combining the 1=2 condition with the column number (if 1=2 and ColumnNo() <> the number of the column you want to hide)

tumrong · ‎2021-09-08

Thank you for generous offer to help. I am afraid I do not have file to send to you. It is Qlik Sense. I do this report on web client like Google Chrome. I really do not know where to get this file. Thank you again.

rbartley · ‎2021-09-08

You export it from the QMC (Qlik Management Console). url = yourservername/qmc/

rbartley · ‎2021-09-08

If you explain what the difference is between the columns you want to hide and the green columns you want to show it would help. Do you want to hide all columns where the values are null? It is not obvious from the picture you supplied.

tumrong · ‎2021-09-08

Please have a look in Excel file. I put the explanation in the file. Thank you again.

rbartley · ‎2021-09-08

The only possible solution that occurs to me is concatenating the two columns into one by using a single measure like this:

= Sum([Shopper Total Spend])
&
if(Not(IsNull(Before(Sum({<[Period]>}[Shopper Total Spend])) - 1)),
' ('
&
num(Sum([Shopper Total Spend])/Before(Sum({<[Period]>}[Shopper Total Spend])) - 1,'##0.0%')
&
')'
)

This produced the following output:-

It might not be perfect, but it's the best I can think of. You will need to change the field names to match those in your app.

tumrong · ‎2021-09-09

Thanks a lot. This is very good solution. But can we do this also

Can we bold the text in the percent change (-15.1%)

Then, if percent change is negative, we display it as red text (-15.1%)

If the percent change is positive, we display it as green text (9.28%)

it would look like this

I appreciate your help a lot. It is really helpful.