topic Re: How to Hide an Expression Field in Pivot Table in QlikView

How to Hide an Expression Field in Pivot Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to Hide an Expression Field in Pivot Table

swuehl — Thu, 31 Mar 2016 21:35:41 GMT

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]

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Thu, 31 Mar 2016 22:18:56 GMT

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

Rgds

Re: How to Hide an Expression Field in Pivot Table

swuehl — Thu, 31 Mar 2016 22:23:04 GMT

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?

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Thu, 31 Mar 2016 23:08:30 GMT

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.

Re: How to Hide an Expression Field in Pivot Table

sunny_talwar — Fri, 01 Apr 2016 01:21:22 GMT

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.

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Fri, 01 Apr 2016 21:06:12 GMT

Thank you Sunny...

Re: How to Hide an Expression Field in Pivot Table

swuehl — Fri, 01 Apr 2016 23:54:01 GMT

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

Re: How to Hide an Expression Field in Pivot Table

sunny_talwar — Fri, 01 Apr 2016 23:56:44 GMT

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

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Fri, 15 Apr 2016 22:57:44 GMT

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.

Re: How to Hide an Expression Field in Pivot Table

swuehl — Fri, 15 Apr 2016 23:11:45 GMT

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.

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Mon, 18 Apr 2016 21:58:17 GMT

Thank you so much! Swuehl ... It finally work. This is exactly what I wanted to appear. I've been stuck for this issue for weeks now.

Thank you once again.

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Wed, 04 May 2016 23:04:18 GMT

Hi! Swuel,

I'm sorry but I have to get back to you being an expert. Though the macro succeeded in hiding the DE column in the pivot table, the DE column showed up again during N print... the macro is being ignored during the Nprint process. Do you know why this happened? Is there something that I need to add in the macro?

Tks.

Re: How to Hide an Expression Field in Pivot Table

swuehl — Wed, 04 May 2016 23:10:55 GMT

Sorry, I don't know. I guess NPrinting is ignoring the table settings for column width and use its own for the printing layout.

But I don't really know NPrinting so you would probably better of creating a new thread in one of the NPrinting places to get help on this issue.

Re: How to Hide an Expression Field in Pivot Table

Anonymous — Wed, 04 May 2016 23:15:28 GMT

ok...Thank you for your time...