topic Re: Re: Pivot Table Total Sum in QlikView

Pivot Table Total Sum

Thu, 04 Sep 2014 17:07:27 GMT

Hi friends,

I have stuck in a requirement see I have a table where I have opening balance and Closing balance with Sales field.Lets assume following table.

Month	Opening	Sales	Closing
Jan	150	20	160
Feb	160	50	200
Mar	200	10	220
Apr	220	80	310
May	310	20	400
Total	150	180	400

as in the above table I have to calculate Total where for opening total is its starting value i.e. 150 and for Closing its last value i.e. 400 and for sales it is sum(sales) .

Does anyone has any idea how to solve this.

Regards

Bhawna

Re: Pivot Table Total Sum

Anonymous — Thu, 04 Sep 2014 17:29:16 GMT

Like the attached ?

I used a Chart of type Straight Table

On the Expression tab for the Expression Total mode I used :

First String

Sum

Last String

Re: Pivot Table Total Sum

gmoraleswit — Thu, 04 Sep 2014 17:38:08 GMT

Here is another solution using the Dimensionality function

Hope this helps

Regards!

Re: Pivot Table Total Sum

Thu, 04 Sep 2014 18:08:38 GMT

I need to use pivot table but thanks for replying I can use this in some another issue.

Re: Re: Pivot Table Total Sum

Thu, 04 Sep 2014 18:14:19 GMT

My requirement is kind of different I need to use some extra field and Cross the column to a row so for me its difficult to put your suggestion.I am sharing a snapshot of my table,hope you will get idea how to do this.

I used Partial sum option thats why Total for opening and closing is showing sum of all value but I need first value for opening and last value for closing.

Re: Re: Pivot Table Total Sum

gmoraleswit — Thu, 04 Sep 2014 18:59:22 GMT

You can use the Dimensionality() function regardless of the number of dimensions you have

Dimensionality() should give you 0 for your grand totals row

Re: Pivot Table Total Sum

Fri, 05 Sep 2014 05:03:59 GMT

But then it will show the opening stock value in each dimension column,means in your example You are considering only A but when you one more dimension suppose B then it will show the value for B but I need separate column for Opening and Closing.