Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I have a hard time reading your last comment, the text seems to be printed all in one line somehow.
But I guess you want to see a solution with the Macro.
See attached (using the Macro from Christof Schwarz's QVW)
Open the module editor using Tools menu to see the macro. I just executed it once using the Test button. Now you could even remove the macro code from your app, if you want.
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]
Thank you Swuehl for your prompt response...I thought we can do it using a conditional formula.
Rgds
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?
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.
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.
Thank you Sunny...
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
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
I am on mobile device. Would love to see what you have done Stefan