topic Multiple expressions in same pivot table column? in QlikView

Multiple expressions in same pivot table column?

Jason_Michaelides — Tue, 22 Jan 2013 00:40:41 GMT

Hi all,

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:

Category should have a partial sum only where the value is SME
Under the SME total within each Region, should be a completely separate expression - Sum(Sales)/Sum(TOTAL Sales), expressed as a percentage.

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

Thanks,

Jason

Re: Multiple expressions in same pivot table column?

Tue, 22 Jan 2013 04:31:41 GMT

hi, post a sample document.

Niranjan M.

Re: Multiple expressions in same pivot table column?

Jason_Michaelides — Tue, 22 Jan 2013 08:28:01 GMT

Hi Niranjan,

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

Thanks,

Jason

Re: Multiple expressions in same pivot table column?

rwunderlich — Tue, 22 Jan 2013 08:29:26 GMT

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'

,sum(Sales)/sum(TOTAL Sales)

,sum(Sales)

)

-Rob

Re: Multiple expressions in same pivot table column?

Jason_Michaelides — Tue, 22 Jan 2013 08:47:21 GMT

Hi Rob,

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!

Thanks again,

Jason

Re: Multiple expressions in same pivot table column?

Tue, 22 Jan 2013 09:17:42 GMT

Hi,

here I used Dimentinality() function.

It works as u wish,plz find the attachement.

Niranjan M.

Re: Multiple expressions in same pivot table column?

Jason_Michaelides — Wed, 23 Jan 2013 00:11:16 GMT

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.

Thanks!