Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Expanding Columns like Excel

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Expanding Columns like Excel

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.

11 Replies
Highlighted
hallquist_nate
Contributor III

Expanding Columns like Excel

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

Not applicable

Expanding Columns like Excel

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.

MVP
MVP

Expanding Columns like Excel

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.

Not applicable

Expanding Columns like Excel

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.untitled.bmp

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

Am I missing some basic functionality in QlikView?

Russ

MVP
MVP

Expanding Columns like Excel

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).

Not applicable

Expanding Columns like Excel

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.

MVP
MVP

Re: Expanding Columns like Excel

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.

Not applicable

Re: Expanding Columns like Excel

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....  

ranajit81
New Contributor III

Re: Expanding Columns like Excel

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.

Community Browser