topic Pivot table / Additional row. in QlikView

Pivot table / Additional row.

Tue, 23 Dec 2014 14:06:15 GMT

Hello all

I have the following PL data. I wanted to create a new CAT value called Gross Margin. But this value does not exist in the Excel document. How can I create this row in Qlikview without tampering with the Excel Document?

I want to use a pivot table to display my results.

Re: Pivot table / Additional row.

Tue, 23 Dec 2014 14:08:52 GMT

You can go to the "Presentation" tab of your pivot table properties. From there Select your dimension (PL Item in this instance) and click 'Show Partial Sums'. This will give you a sum total of all the sales entries.

Re: Pivot table / Additional row.

Tue, 23 Dec 2014 14:14:56 GMT

Hi Jacob

That I agree, but its very cosmetic. As you can see in the example below, if I only have Sales and COS, I get the correct Gross Margin value. However, if I include Heads, the Gross Margin value is incorrect.

Isn't there a way of creating the calculations and physically having a line called 'Gross Margin' rather than having it as a Total?

Re: Pivot table / Additional row.

saumyashah90 — Tue, 23 Dec 2014 14:22:36 GMT

Hi Sabah,

Please check my demo qvw.

Re: Pivot table / Additional row.

Tue, 23 Dec 2014 14:45:04 GMT

Hi Saumya,

Thanks for that reply.

Ok - so i'm working off from your idea, the first table I have tried to create the calculation for the Gross Margin, which is as follows:

if( CAT = 'COS', Sum({<CAT={'COS'}>} Sales),

if( CAT = 'Sales', Sum({<CAT={'Sales'}>} Sales),

if( CAT = 'Gross Margin', (Sum({<CAT={'Sales'}>} Sales) + Sum({<CAT={'COS'}>} Sales)),'')))

But, the value for Gross Margin still appears as zero.

Can you guide me?

Re: Pivot table / Additional row.

saumyashah90 — Tue, 23 Dec 2014 14:49:31 GMT

If this is your final requirement ,you where going on wrong path

Check this qvw

Re: Pivot table / Additional row.

Tue, 23 Dec 2014 15:07:00 GMT

Hey again,

Again, this goes back to my previous query as you can see in the example below, if I only have Sales and COS, I get the correct Gross Margin value of 383977.

However, if I select 'Heads' for example, I don't want this to be a part of my Gross Margin total. Thats a seperate value.

In effect, I am looking for a result like this in Qlikview