Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create an Expandable Table

Hello everyone,

This is how my pivot table looks like:

                             John Doe      John Smith     Jane Doe

June

July

August

However, is there a possible way to make it look like something this:

                         Employees

June

July

August

And then under employees, there will be that + sign to expand and see what's inside Employees

Thank you

3 Replies
swuehl
MVP
MVP

Create a field in your data model with a constant 'Employees', like

LOAD

     Employee,

     'Employees' as EmployeeDim,

     ...

Then use EmployeeDim and your existing Employee field as dimensions in your pivot table chart (plus the Month field).

johnw
Champion III
Champion III

I'd probably do it just as swuehl‌ says.

But just to toss out a different idea that isn't exactly what you asked for but is similar in flavor, sometimes I'll create a value of 'Total' in a field [Total] disconnected from my data set, then create cyclic group(s) with [Total] and whatever field(s) I might want to break that total down by in the chart(s). In this case, something like:

[Employees]: LOAD 'Employees' as [Employees] AUTOGENERATE 1;

And then create an EmployeeGroup with [Employees] and [Employee] and use the group as the dimension.

But it's a pivot table, so people will probably be more used to looking for the + button, and will know what it does where they won't know until they try it what clicking on the little cycle icon will do. So probably best to stick with just putting both fields in the pivot table. You could still leave [Employees] disconnected from the rest of your data model, though. A little more script, a tiny bit less memory used. I doubt speed would be any different either way, but I don't know. The 'Total' approach is really more for when you have more than one way you might want to break the data down by, or you might just want to look at the total.

swuehl
MVP
MVP

Just as a side note, John, I think a constant value won't occupy any memory (in the bit stuffed pointer) in the record table (that's something I learned recently in a discussion following one of Henric's blog posts). You can see this when looking at the field bit information when storing this table to a QVD.

https://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointer...

Symbol tables should be mostly the same in both cases.