Expanding Columns like Excel

[#1]

Hello all,

This keeps coming in as a user request.

In Microsoft Excel you can group a set of columns together so they have a total but you can hide and show the details.

What's the best way to do that with a straight table or a pivot table?

Russ

[hallquist_nate]

Pivot table.  It gives the opportunity to what you are looking for, and you can drag dimensions and reorganize as you would in Excel.

[#3]

I took a few minutes and dug deeper and it's relatively easy to do with a straight table.  With a pivot table to make 'collapsible' columns I'm pretty sure you need macro code.

[johnw]

If I understand what you mean by "collapsible", you'd just click on the column heading the same way you click on rows to collapse them.  Well, that's assuming your column headings are dimensions, and not just expressions.

[#5]

Hi John,

I have diehard excel users who want this functionality within a straight or pivot table.  See the image for what I mean.

This is what I"m trying to emulate.

On the left, you see the totals with the details hidden.  On the right exposed.

Am I missing some basic functionality in QlikView?

Russ

[johnw]

Ah, OK, what I said doesn't quite work because there's no column heading to collapse in your example.  However, you can simply add one.  Add a calculated dimension ='Total' and drag it to the top of the pivot table.  Expanded, you'll see all the columns and the total.  Collapsed, you'll just see the total.

I'm not thinking of a way to do it with a straight table except to convert it to a pivot table (or deal with the headache of macros, which I don't suggest).

[#7]

Slightly off topic... macros = bad, then why have them?

For the straight table for the columns I want to hide/show I have a conditional show clause and then I use a checkbox to hide/show them, so that works in that case.  Works and it's macro free.

[johnw · Accepted solution]

Oh, duh, I've done the straight tables before almost exactly like you said.  I can't believe I forgot.  In my case, I wanted it to look just like a pivot table expand/collapse, so it was slightly more trouble, but the same basic idea.  Make a text object with text =if(vShowDetails,'-','+') and action "set variable, vShowDetails, =not vShowDetails".  Make it look as close to the expand/collapse icon as you can.  Set the detail chart expressions to have a conditional show of vShowDetails.

As for macros, they exist for when they are the ONLY (or in much rarer cases, the simplest) way to accomplish something.  I have applications with some pretty complex macros.  They're necessary.  But almost any other way of accomplishing the same thing is preferable.

[#9]

Given the amount of time you've spent with the QlikView you've probably forgotten more than I know!  (Hate that expression but I think it applies here).  I've only been using it since last August.

To do the same thing with a pivot table I thought using macros to set the column widths to zero and using the same visual cue as you mentioned would perform the same purpose.

I remember the old 'Sheridan Controls' for Visual Basic that had a lost of custom grid controls I used on other projects.   We need the same for QlikView.  If I only had time....  

[Anonymous]

ok, you can do it with columns. but can us do the same with rows? favourably without a macro? or if macro is a must, is it possible? Thanks.