topic Special Case For One Row In Pivot in QlikView

Special Case For One Row In Pivot

Tue, 01 Feb 2011 18:02:01 GMT

I have a pivot table shown below. It breaks sales down by regions and sales lines. It shows the sum of the sales for different region-line combinations. This works ok but I have another requirement that i'm not sure how to meet. The last column is called America's Take. Even if a sale is done in another country there is some amount that is still considered part of Americas. The requirement is that the Americas sales subtract the Americas Take values from it's total. So the first row that says "America | Sales1 | 300 | 0" should be "America | Sales1 | 215 | 0"

I've tried doing this for the Sales expression:

=
SUM(SaleAmount)
-
if(REGION_ID = 1, SUM({$<REGION_ID=>}TakeAmount), 0)

with no luck.

Attached is a example QVW. Thanks!

Special Case For One Row In Pivot

matt_crowther — Tue, 01 Feb 2011 18:27:25 GMT

Soloution attached - hopefully!

Good old 'Total' to the rescue.

Hope that helps,

Matt - Visual Analytics Ltd

Special Case For One Row In Pivot

Tue, 01 Feb 2011 18:46:18 GMT

Thanks so much. Whenever I ask a question the solution seems so simple but I never have any clue. Thank god for this forum.

Special Case For One Row In Pivot

Wed, 02 Feb 2011 22:34:21 GMT

Hey Matt,

I tried turning this into a straight table instead of pivot so we could have totals but i'm having some issues. As you can see in the screen shot below the Sales column has a total of 1350 but it should show 1265. Any ideas?

Special Case For One Row In Pivot

Wed, 02 Feb 2011 22:36:46 GMT

Never mind, just realized there is a sum of rows option. Thanks again!