Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Multiple expressions in same pivot table column?

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:

  1. Category should have a partial sum only where the value is SME
  2. 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

1 Solution

Accepted Solutions
Not applicable

Hi,

here I used Dimentinality() function.

It works as u wish,plz find the attachement.

Niranjan M.

View solution in original post

6 Replies
Not applicable

hi, post a sample document.

Niranjan M.

Jason_Michaelides
Luminary Alumni
Luminary Alumni
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni
Author

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

Not applicable

Hi,

here I used Dimentinality() function.

It works as u wish,plz find the attachement.

Niranjan M.

Jason_Michaelides
Luminary Alumni
Luminary Alumni
Author

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!