topic Expanding Columns like Excel in QlikView

Expanding Columns like Excel

Thu, 19 Apr 2012 19:41:40 GMT

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

Expanding Columns like Excel

hallquist_nate — Thu, 19 Apr 2012 20:00:16 GMT

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

Expanding Columns like Excel

Fri, 20 Apr 2012 19:47:44 GMT

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.

Expanding Columns like Excel

johnw — Fri, 20 Apr 2012 23:41:18 GMT

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.

Expanding Columns like Excel

Mon, 23 Apr 2012 14:10:11 GMT

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

Expanding Columns like Excel

johnw — Mon, 23 Apr 2012 17:13:23 GMT

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

Expanding Columns like Excel

Mon, 23 Apr 2012 17:23:33 GMT

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.

Re: Expanding Columns like Excel

johnw — Mon, 23 Apr 2012 18:06:37 GMT

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.

Re: Expanding Columns like Excel

Mon, 23 Apr 2012 18:34:16 GMT

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

Re: Expanding Columns like Excel

Anonymous — Thu, 21 Jun 2012 16:01:18 GMT

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.

Re: Expanding Columns like Excel

Anonymous — Thu, 21 Jun 2012 16:01:23 GMT

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.

Re: Expanding Columns like Excel

geam1988 — Thu, 04 Oct 2018 11:50:46 GMT

Hello John,

I have 2 dimension and 19 expressions in a report.

6 expressions are sales related, 9 expressions are production cost related and 4 expressions are administration cost related. I want to collapse expression and it should be expandable on a click. Something like + , - option. i did something like this as per your comment but it is not working. Any suggestion ?

Best Regards

kazi

all expressions with condition like above

I am not sure, what should i do with variable Overview. there i did now write anything.