Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to Hide an Expression Field in Pivot Table

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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.

View solution in original post

14 Replies
Highlighted
MVP
MVP

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]

Highlighted
Creator II
Creator II

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

Rgds

Highlighted
MVP
MVP

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?

Highlighted
Creator II
Creator II

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.

Highlighted

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.

Highlighted
Creator II
Creator II

Thank you Sunny...

Highlighted
MVP
MVP

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

Highlighted

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

Highlighted
Creator II
Creator II

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.