How to hide an expression in pivot table depends on Dimension values

[krishna20]

Hi Folks,

I'm having three dimensions in pivot tables for eg; X, Y, Z.

X and Y are in vertical position and Z  is in horizontal position.

Z having values A, B, C.

I'm writing five expressions. Obliviously  We can see five expressions below the each value (A, B,C) i.e; Under A five , B five, and C five expressions.

I want to see only two expressions under A, Three expressions under B which are not in A. Five expressions under C .

Please help me out how to achieve this logic friends.

Regards

Krishna

[Colin-Albert]

I think you will need to use a straight table chart  to do this not a pivot chart.

You will need to add a separate expression for each column of your chart.

You cannot hide columns in a pivot chart.

[krishna20]

Hi Colin,

Thank you for your valuable reply. There is no way to achieve this in pivot table by using conditions in expressions itself?? Because , my client is not compromising to approach another way. They want to see this data in pivot table only.

[Peter_Cammaert · Accepted solution]

It's possible, but not too simple. You should define an artificial dimension that controls the five expressions.

This can be done using an INLINE table or a ValueList() calculated dimension.

See Hide a column in Pivot Table‌ or any other discussion that deals with this issue. There are a lot of them.

Best,

Peter

[krishna20]

Hi Peter,

Edit :

Not Getting Expression (Case Pack)in Warehouse and should not get Avail OH and Store in Store.

My Dimension

=ValueList('Avail OH','Case Pack','SOHMIN','SOHMAX')

Expression :

=IF(ValueList('Avail OH','Case Pack','SOHMIN','SOHMAX')='Avail OH',Sum(APPRSOH),

IF(ValueList('Avail OH','Case Pack','SOHMIN','SOHMAX')='Case Pack',Sum(MM_CASEPACK),

IF(ValueList('Avail OH','Case Pack','SOHMIN','SOHMAX')='SOHMAX',Sum(MM_SOHMAX),

IF(ValueList('Avail OH','Case Pack','SOHMIN','SOHMAX')='SOHMIN',SUM(MM_SOHMIN)))))

Please suggest me where i goes wrong.

Regards

Krishna

[Peter_Cammaert]

Most probably this particular expression only produces 0 or NULL values for WAREHOUSE.

Your expressions now follow typical dimension display logic: if there is nothing to show for this dimension value, it will be suppressed. That's also one of the things you cannot do otherwise (by way of a Conditional Show or other settings or tweaks)

[krishna20]

Finally i came to know. It's not possible to do so .

Thank you peter for lightening up on this issue.

Regards

Krishna

[krishna20]

Hi Peter,

Is this possible to do this in Macros?? If yes please share some coding .