17 Replies Latest reply: Apr 17, 2015 2:07 PM by Sunny Talwar

# Subtotals by data values in fields - straight and pivot table

Can anyone tell me how to make subtotals by data values in a particular field.  Pretty sure this will be a straight table I do this on.  See the attachement for an excel example.   Thanks MA

• ###### Re: Subtotals by data values in fields - straight and pivot table

Subtotals are available in the pivot table and you can find them on the Presentation tab of the chart properties (it is referred as Partial Sum)

HTH

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

thats not what Im looking for.    lets say I have 2 columns.    color and cost

color      cost

red          1

blue          1

orange     2

yellow     1

brown     4

green     2

so now I want  asubtotal on  red and blue , one for orange   one for yellow,brown and green.  then a grand total of all colors.   I can get the grand total, but dont know how to get the subtotal of specific values of the field color.

• ###### Re: Subtotals by data values in fields - straight and pivot table

I apologize for not understanding your question earlier. I am still unsure about how you are grouping different color? Is there a logic behind putting Red and Blue together and leaving Orange alone and then combining Yellow, Brown and Green?

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

no no logic as I would be using different data, the colors was just a made up example to help in understanding what I was asking

• ###### Re: Subtotals by data values in fields - straight and pivot table

What I mean is, QlikView needs to know where exactly to put this subtotals. One day you want it to be Red and Blue and next day you would want it to be Red and Yellow. There needs to be some kind of logic to group things, right?

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

the clients tell us what values go together and they will always stay together unless at some point down the road they want to add or change the values.

• ###### Re: Subtotals by data values in fields - straight and pivot table

Makes sense, Let me see if I can get your subtotals to work...

• ###### Re: Subtotals by data values in fields - straight and pivot table

thanks tons   Ive tried the demensions hack but that really didnt work as I had to add more fields (place holder)

• ###### Re: Subtotals by data values in fields - straight and pivot table

See if this is helpful. (PFA)

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

that sure is on the right track!!    I would like the word total to be a seperate line in color field, and not sure I understand why there are 2 cost columns.     in my original post I have a picture of what they are looking for (you know can you take this excel spreadsheet and put it in QV but make it look like this....)  Your definately on the right track

• ###### Re: Subtotals by data values in fields - straight and pivot table

Two cost columns because subtotals for each would show only if I have more than 1 dimension here. So I added Cost as another dimension. Let me see if I can fix the color thingie.

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

See if this is helpful (PFA)

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

yes yes thats what I need (well I dont need the first column but surely I can hide that (1,2,3)    Totally cool  do you have an explanation of how you did this?

• ###### Re: Subtotals by data values in fields - straight and pivot table

im assumming i can put a grand total in there also...

• ###### Re: Subtotals by data values in fields - straight and pivot table

THANK YOU so much!

• ###### Re: Subtotals by data values in fields - straight and pivot table

Not a problem, I am glad I was able to help.

Best,

Sunny

• ###### Re: Subtotals by data values in fields - straight and pivot table

I just created the groupings in the script and then used partial totals for color within each Group. You can creat the subtotal by adding a partial subtotal for Group.

HTH

Best,

Sunny