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.
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:
[Nice side effect, it also describes a very useful pivot table sorting approach]
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?
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...
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.
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.