I'm wondering if anyone can come up with a way of achieving the following. Every way I try gets really messy! My business has been used to things a certain way (in Excel, of course!) for a long time. They are largely embracing a different way of thinking with QlikView but we still have one or two sticking points to get through. One of them is as follows:
I have a pivot table with 3 dimensions, Region, Category and Customer. There is a single expression Sum(Sales). Category is one of two values - SME, or Enterprise.
My requirement is two-fold:
I know this is completely against the logic of a pivot table - I just wondered if any of you lateral thinkers out there had a bright idea...!?
The above is a much simplified explanation to illustrate the point. However I have put together an example. In the attached application, I only want partial sums where Category = SME and I don't want the % expression in a separate expression. It should only show under the SME total within each region (i.e. just the regional %)
Take a look at the QV Cookbook (http://robwunderlich.com/downloads/) example "Alternate format for chart Total rows."
You can test if you are on a total row using rowno(). Your expresion would look something like:
=if(rowno()=0 AND Category='SME'
Thanks, however RowNo() only seems to work for the last dimension. The above is a simplified example - I actually have other dimensions after Customer above.
However, it has been a couple of years since I have looked at your excellent Cookbook and there's more in there that will be of great use now!
You have answered the simplified version I asked about - thanks Niranjan. I did play with Dimensionality() but didn't think of the formatting inside the expression. After following your lead and some more fiddling I got to the rather more complicated vesion I needed for my real problem.