topic Re: Subtotals by data values in fields - straight and pivot table in QlikView

Subtotals by data values in fields - straight and pivot table

Anonymous — Fri, 17 Apr 2015 16:21:05 GMT

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

sunny_talwar — Fri, 17 Apr 2015 16:24:04 GMT

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

Anonymous — Fri, 17 Apr 2015 16:43:27 GMT

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

sunny_talwar — Fri, 17 Apr 2015 16:47:58 GMT

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

Anonymous — Fri, 17 Apr 2015 16:51:44 GMT

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

sunny_talwar — Fri, 17 Apr 2015 16:54:47 GMT

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

Anonymous — Fri, 17 Apr 2015 17:03:35 GMT

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

sunny_talwar — Fri, 17 Apr 2015 17:06:29 GMT

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

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

Anonymous — Fri, 17 Apr 2015 17:09:42 GMT

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

sunny_talwar — Fri, 17 Apr 2015 17:24:02 GMT

See if this is helpful. (PFA)

Best,

Sunny

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

Anonymous — Fri, 17 Apr 2015 17:33:49 GMT

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

sunny_talwar — Fri, 17 Apr 2015 17:36:12 GMT

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

sunny_talwar — Fri, 17 Apr 2015 17:45:46 GMT

See if this is helpful (PFA)

Best,

Sunny

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

Anonymous — Fri, 17 Apr 2015 17:53:07 GMT

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

Anonymous — Fri, 17 Apr 2015 17:53:40 GMT

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

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

sunny_talwar — Fri, 17 Apr 2015 17:57:29 GMT

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

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

Anonymous — Fri, 17 Apr 2015 18:06:56 GMT

THANK YOU so much!

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

sunny_talwar — Fri, 17 Apr 2015 18:07:54 GMT

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

Best,

Sunny