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: 
Not applicable

How to calculate subtotals in a pivot table for a particular column values?

Hi All,

Please refer to the attached xls file.

I am importing data from this xls file into a pivot table. I have two columns in the excel, that is, Score and Category. I need to calculate the total score for different categories.

For example:

Category Score

customers 10

20

5

SubTotal: 35

Internal Processes 20

10

30

SubTotal: 60

Pls. suggest how to represent data in this form in a Pivot table in QV?

Regards,

Swati Bansal

11 Replies
Not applicable
Author

Hi Swati,

In pivot table chart properties, go to presentation-go to "Dimensions and Expressions" (top left corner) select category and tick show partlal sums (below rectangle).

You will get such results.

Happy Thoughts

Not applicable
Author

Hi Ravi,

I tried this but nothing was calculated in total. Secondly, I have a query. How does QV know that it has to calculate sub totals for the Score column, as nowhere we have mentioned that totals are to be calculated for Score values.

Regards,

Swati

Not applicable
Author

Hi swati,

I hope your Category is in Dimensions.

Can you post Excel file for which you want this subtotal?

By ticking Show partial sums, we instruct QV to give subtotal for that particular dimension.

Not applicable
Author

Hi Ravi,

I am attaching the qvw file for your reference. The xls is the same I have attached in my previous posts.

Actuallly I wanted to calculate subtototals for the score column, but score was earlier an Expression. Even after converting it into Dimension, i am not able to calculate the total of score values for different categories.

You can try yourself from the attached qvw file.

Regards,

Swati

Not applicable
Author

Hi Swati,

Problem is identified.

Plz refer to the attachment.

First table "Scorecard" that you had created was a "straight table".

I converted it to "Pivot Table".

You can get such subtotals only in Pivot table not in Straight table.

Happy Thoughts

Not applicable
Author

Hi Ravi,

I have used pivot table only. Pls. see the attached file.

I have a category column which has two values:Customers and Internal Processes.

Another column is Score.

Now I need to calculate the total score for Customers and Internal Processes.

Pls. suggest how to achieve this. I cudn't do it with the approach you suggested.

Thanks & Regards,

Swati Bansal

Not applicable
Author

Hi Swati,

Plz apply "Show Partial sums" on both "Category" and "Type".

You will get it.

Let me know.

Sincere Regards,

Happy Thoughts

Not applicable
Author

Hi Swati,

Also, in expression, you have written "Score".

Use sum(Score).

Plz see attached file.

Not applicable
Author

Even after applying partial sums on both Category and Type, I don't get the desired result. It doesn't give me the total of the "Score" column. You can see in the attached qvw file.

Regards,

Swati