Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
krishna20
Specialist II
Specialist II

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

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

7 Replies
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
Specialist II
Specialist II
Author

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
Partner - Champion III
Partner - Champion III

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
Specialist II
Specialist II
Author

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)))))

Expression_prob.PNG

Please suggest me where i goes wrong.

Regards

Krishna

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Specialist II
Specialist II
Author

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

Thank you peter for lightening up on this issue.

Regards

Krishna

krishna20
Specialist II
Specialist II
Author

Hi Peter,

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