How to Hide an Expression Field in Pivot Table

[Anonymous]

Hello!

I have  two expressions in pivot table. I am using the first expression(DE) to derive the second expression. (the column after DE)..Thus, I have to enable it.. just want to hide it and leave only  the 2nd expression to show that depends on the on the 1st expression without changing its values.  I tried to enable the DE column and the value in 2nd column were gone.

I also tried using a conditional false()... it worked but the 2nd expression got affected.  This table will be sent out to our customers and it's not good for for them to see 2 expressions in 1 dimension when they don't need the 1st.   Currently, I formatted the text color to white in DE column so they look like empty cell...  If it's not possible to hide an expression in the pivot table , how can I at least delete the 1st expression during Nprinting.

Pls. advise.

[swuehl]

I don't think you can hide the column, but you can set the column width to zero using a macro code once,

See this blog post and the attached QVW for an example how to do that:

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

[Nice side effect, it also describes a very useful pivot table sorting approach]

[Anonymous]

Thank you Swuehl for your prompt response...I thought we can do it using a conditional formula.

Rgds

[swuehl]

Well, maybe I just not fully understand your setting.

Could you post a small sample QVW?

Or describe in more detail your dimensions and expressions and what you've tried with your conditional?

[Anonymous]

Hi!  Swuehl,

Pls. see attached...Don't worry about the computations ...they're  wrong.  But my table looks like in my sample file.  I want to hide my column without affecting the values in the 2nd expressions as the 2nd expression  is dependent on the 1st expression DE...

Tks.

[sunny_talwar]

Conditional hide will not work in this case since you are using an expression which isn't available. In this case, Stefan's one time macro might work better for you.

[Anonymous]

Thank you Sunny...

[swuehl]

Seems I missed something, I haven't noticed a conditional expression used in that chart.

So where have you used 'I also tried using a conditional false()... it worked but the 2nd expression got affected' in your chart?

As an alternative to setting the column width to zero for the first expression, I could imagine that you manage to create a single expression, combining the two existing. Maybe using advanced aggregation (aggr() function) to calculate your accumulated values, or maybe using an AsOf table approach.

Pitfalls of the Aggr function‌

The As-Of Table

One problem may be that you don't show data for each combinations of dimension values, so your pivot table shows mostly 'missing data'.

Attached a draft what I envisioned using advanced aggregation, far from complete or correct, but since you said the current calculations are wrong anyway, it shouldn't matter

No, seriously, still some way to go.

Start with transforming your pivot table into a straight table and try to calculate your DE expression without using the expression label as reference in the same expression.

Stefan

[sunny_talwar]

I am on mobile device. Would love to see what you have done Stefan

[Anonymous]

Hi Swuehl,

Sorry for the late response...been busy for an urgent project.

I can't transform my pivot to Straight table as my Weekly buckets are  dimension fields which I make them into horizontal...

If you could provide me a macro to delete the "DE field"  ( without changing the values in the 2nd column, of course ) ...I would really appreciate it...

Thank you so much.