topic Re: Totals Row with Specific Values in QlikView

Totals Row with Specific Values

Anonymous — Tue, 09 Aug 2016 15:32:22 GMT

Hello. I am wondering if there is a way to create a separate totals row in addition to the grand totals row in a pivot table. For example, if I have the table below, here is what I am trying to do:

M&M's

Red 6

Green 4

Blue 2

Yellow 5

===========

Red and Blue subtotal 8

Grand Total 17

How do I create the conditional subtotal? Thank you

Re: Totals Row with Specific Values

jpenuliar — Tue, 09 Aug 2016 16:39:28 GMT

Hi Robert,

Try and make use of Dimensionality() function.

Example in expression :

=If(Dimensionality() = 0

,Sum({<Colour = {'Red','Blue'}>}[M&M]) & chr(13) & Sum([M&M])

,Sum([M&M])

)

should look like below:

Re: Totals Row with Specific Values

jpenuliar — Tue, 09 Aug 2016 16:40:42 GMT

Label for Totals:

=If(Dimensionality() = 0

,'Red and Blue subtotal' & chr(13)& 'Grand Total'

)

Re: Totals Row with Specific Values

Anonymous — Tue, 09 Aug 2016 17:48:09 GMT

What if the values are calculated? For example, if the value for blue M&M's is actually created using a formula and not a straight value from the table?

Re: Totals Row with Specific Values

jpenuliar — Tue, 09 Aug 2016 17:52:26 GMT

hmmm, I suppose the logic should be Red & Blue = exressionRed + expressionBlue.

Can you post a sample data/app?

Re: Totals Row with Specific Values

Anonymous — Wed, 10 Aug 2016 18:12:03 GMT

Jonathan I have tried your suggestions (modifying my data for security purposes and creating a view of straight data) and so far have a table that looks like this:

I used this code to create the table above:

=If(Dimensionality() = 0,Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month]) & chr(13) & Sum([Current Month]),Sum([Current Month]))

and this for the label:

=If(Dimensionality() = 0,'Missing Total' & chr(13)& 'Grand Total')

what am I doing wrong? I am trying to make my table look like this:

Re: Totals Row with Specific Values

jpenuliar — Thu, 11 Aug 2016 09:19:25 GMT

You probably need to tick MultiLine Setting> adjust Wrap Cell Height to show the next line created by chr(13)

Re: Totals Row with Specific Values

Anonymous — Thu, 11 Aug 2016 14:50:04 GMT

OK Jonathan I am much closer now. Here's my table:

I am trying to figure out why my percentage totals are so far off now. Thank you for your help