topic Re: Sum of Columns in Pivot table in QlikView

Sum of Columns in Pivot table

Sat, 12 Mar 2016 15:55:33 GMT

Hi all, I'm not sure if I'm missing a trick but I have a requirement where I have to show the Sum of measures of a row in a Pivot table but with dimensions going across and the measures going down, e.g.

Any help appreciated!

Many thanks

Re: Sum of Columns in Pivot table

oknotsen — Sat, 12 Mar 2016 16:07:11 GMT

Go to the Presentation tab and in the top-left corner, turn on "Show Partial Sum".

You might need to play a bit with on which dimension to turn this on before you figured out which is the correct one.

Re: Sum of Columns in Pivot table

Saravanan_Desingh — Sat, 12 Mar 2016 16:32:04 GMT

Are you using the Sales as Dimension or Customer?

The first Table shows Customer as Dimension and the second shows Sales.

I hope you have not Transposed the Dimension to Column.

If possible, please upload the App.

Re: Sum of Columns in Pivot table

Sat, 12 Mar 2016 16:32:05 GMT

Hi, thanks for the reply, however I did already try this but it simply moves back the Dimensions from column format (across the top) back to row format, I think it needs maybe another Expression to be used as a Total of all values for a row but really not sure.

Re: Sum of Columns in Pivot table

Kushal_Chawda — Sat, 12 Mar 2016 16:43:18 GMT

Create the the Expression Total using column(1)+column(2)

Now you cab simply drag the expression to Column and drag the dimension to raw if you are using pivot table

Re: Sum of Columns in Pivot table

Digvijay_Singh — Sat, 12 Mar 2016 16:46:55 GMT

See if this can help.

Re: Sum of Columns in Pivot table

Sat, 12 Mar 2016 16:47:45 GMT

Hi, the original source data is in the format of the second table (i.e. Company Name being a column, not a Dimension), however on import I have modeled it correctly having Company as Dimension and sales as measures, the problem is I still need to display it in the old format having Company as Columns and the sales measures appearing in Row format.

I currently have this Pivot chart in Qlikview but just can't get the Total to show as the last row so summing all sale types per company.

Re: Sum of Columns in Pivot table

Sat, 12 Mar 2016 16:55:52 GMT

Many thanks guys, I was hoping not having to hard code all the fields as I have about 20-30 of them but it does look like in this instance I'm rather limited on solutions.

I've got a macro which creates the chart anyhow so will just try added some extra logic to create the Total expression field.

Cheers!

Re: Sum of Columns in Pivot table

oknotsen — Sat, 12 Mar 2016 18:26:18 GMT

Not sure what you mean with "hard code all fields"?

Re: Sum of Columns in Pivot table

Sat, 12 Mar 2016 21:01:53 GMT

Well in my fact I have lets say 20 expressions, to sum all of them wouldn't I need to add each field name by hard coding the field names or doing Column(1) + Column(2) .., or is there a dynamic way of doing this, something like

=concat('if(isnull([' & [FieldName] & ']),0,' & '[' & [FieldName] & ']' & ')' & ' + ')

Thanks!

Re: Sum of Columns in Pivot table

Digvijay_Singh — Sun, 13 Mar 2016 03:41:01 GMT

Check this, I created variable in script and later used in expression -

Re: Sum of Columns in Pivot table

Digvijay_Singh — Sun, 13 Mar 2016 03:42:09 GMT

Let vNoOfFields = NoOfFields('T1');

Let vConcat='';

For i = 1 to vNoOfFields-1

Let vConcat = vConcat & 'Sum(['&FieldName(i+1,T1)&'])+';

vConcat = Left(vConcat,Len(vConcat)-1);

Re: Sum of Columns in Pivot table

Sun, 13 Mar 2016 19:10:08 GMT

Yes I think building the string in the load script looks like the best approach, thanks for the tip!!!