Optimize Pivot Table w/ Expression Headers

[#1]

Hey Guys,

I have a requirement to recreate a report produced from SSRS. Unfortunately the reports have metric groupings. I researched and couldn't find any great way to do this so I did a combination of ideas.

There is already an ad hoc component in the app so I have a data island with the expression's script and the expression's labels. After that I added an additional table with is linked to the Expressions table, which has expression grouping by report.

In the report I have a dimension 'Store Name'. This is the only real dimension I have in the report which all the measures are reporting on. Then I have two "fake" dimensions. Expression group and expression label. I then implemented a technique I saw on here, I created a pick function in a variable by looping through the expressions. So in the report it picks the correct expression script based on the expression label.

The result is something like this:

At this point, I have what I am looking for. I have measures broken out by stores and then their associate measure grouping, Today, WTD, MTD.

My issue is that is takes forever to calculate. It takes almost 3 minutes for the initial render. Meanwhile I have a report that is the same thing but with out the "fake dimensions" has all the expression and it takes at most 20 seconds. Unfortunately, that layout is not the users want or are used to.

I was under the impression pick() was a fast function. Could it be that because it is picking between 30 values, that it is slow? 30 pick functions x 30 pick values x 107 Stores -> Evaluate the picked expression.  Is it that or is it because I am using data island fields in the same object that uses the normal data model. I imagined that the the lack of relationship would cause a minimal delay, but this is much worse.

Any idea or suggestions on what I should do to minimize the calculation time or  how to achieve the layout in the above picture using a different method?

[Gysbert_Wassenaar]

I don't see why you need that artificial grouping dimension and one expression. Why not simply 12 expressions? You could put text boxes above (or on top of) the pivot headers if you want some kind of 'grouped' layout.

Actually, a straight table might work as well or better as a pivot table if you do that.

Without having a good look at your data model and the expressions you use it's very hard to give advice. It would help if you could post a small qlikview document that illustrates the problem.