Fixed number of columns in pivot chart – divided in uneven column groups

[#1]

Fellow Qlikviewers

I have a problem (or call it a challenge) that I really hope you have time to help me with, please J

To make a long story short: Client has certain expectations on the layout of a chart showing (put simply) actual sales vs projected sales in terms of both quantity and amount. And it is actually feasible in its general form.

Please see attached document.

There are two demands that make this chart a bit tricky:

1. The facts of the chart should be divided into two groups of columns - one for amounts and one for quantities. And they should each have a column header spanning the cells of the group.

2. The two groups are not to have the same amount of columns - the "Amounts" column group should have one more column showing amount pr. unit.

As you see in the attached sample I have actually solved this part.

Now here comes the issue at hand:

The client (and I fully agree) wish that the columns of the chart stay the same no matter what SalesPerson or Region (or other dimensions that I didn't include in my example) you select. The problem is that if you select SalesPerson "Skinner" then both "Projection…" columns disappear since he has no projections. Logical QlikView wise - not so logical user wise J

Please advise me how to make the column-layout stay the same regardless of the selected dimensions.

I've tried unchecking "Suppress Zero-values" and checking "Show All Values" on the ColumnName dimension. This brings back the "Projection…" columns but also introduces the "Average…" column in the "Quantity" group which is a no-go.

I'm very open to suggestions - even if it would involve a complete redesign of the solution. Please be aware though that this is a very simplified example to show the general concept of the problem so other solutions might not fit the complete picture.

But I guess my final question is: Is this even possible to do in a reasonable manner?

Thanks a lot!

Jacob

[#2]

Hi Jacob,

I've attached a sample of what you could do. I changed the script slightly.

It's a slightly different view to your original, but it shows the same idea.

Regards,

Xena

[llauses243]

Hi Jacob,

This is my offer ... only add lines to script (see attach)

good luck , Luis

[#4]

Good one...Luis has derived an effective oneliner... .

Cool Luis

[#5]

Nice!

[#6]

Guys,

Thank you so much for taking your time.

Xena I really appreciate your ideas. Funny I didn't think of the same but I guess it's always like that J

But I'm still torn between View1 and View2. View1 is good because it maintains the layout of 1 row pr dimension value. But I failed to mention that the Amount and Quantity groups are only the first ones to arrive - later we are going to add more groups like for instance "Profit". Therefore these groups must be on the top level. View2 is probably the most acceptable solution although I will have a hard time persuading the client into this 2-row layout. But thanks for helping me think out of the box.

Luis what you suggest is pure magic J I'm not yet fully confident in how it works but I will try it out in a greater scale shortly. How do you identify which fields to "associate on" - as in what fields should the 2 joined tables of "Complement" have in common? We are working with around 10 different dimensions of which 4 are part of the chart. I will play around with it and see if I can come up with some general rule.

Furthermore I guess it would be preferable to to rid of the generated synthetic keys? My first attempt will be to replace the key fields with a composite key. But I'm not sure if this will break the magic?

Thanks so far. I will post the results shortly.