Subtracting Row Totals

[umartareen]

Hi,

Can anybody please show me how to subtract the third row from rest of the following rows and display it in the 'Total' row in a Pivot Table?

Where Third row = The sum of first two rows, that is, Golf Estate Total = Golf Estate (Fairway West) + Golf Estate ll & Polo Suites.

For a better understanding of what I'm trying to do, please find the files attached. The first file shows the result that is supposed to be displayed and the second file shows what I have been getting.

Regards

Umar

[datanibbler]

Hi Umar,

I understand you're getting the value of the third row duplicated since that is made up of the two lines above.

I haven't gone deeply into trying that, but - with the default QlikView functions, at least - it is not possible. The sum is just a checkbox in the dialog, you cannot configure anything about it except whether it appears on the top or at the bottom of the table.

You'd have to calculate the total in the script instead (aggregate the figures by month) and just display it as another field - that would then be displayed on the right, however, unless you take the trouble of creating another table underneath the first with just one row - the totals on a month_dimension, same as the first.

Best regards,

DataNibbler

[Anonymous]

Hi,

We have function called "Dimensionality()", you may utilize that to achieve the result. For example,

If(Dimensionality()=0, Sum({$<Collection-={'Golf Estate Total'}>} Collection_Amount), Sum(Collection_Amount))

[umartareen]

Thank you DataNibbler and Satyadev Jaiswal !

[tobias_klett · Accepted solution]

Hi Umar,

there are serveral options to do something like this.

1. Add the collection "GOLF ESTATE - Total" to the data and do a conditional expression.

2. Add a linktable with a new field collection_grp in which you group "Golf Estate - (Fairway West)" and "Golf Estate II & Polo Suites" also to "GOLF ESTATE - Total"

1.1. Concatenate Collection-Table in Script:

concatenate (Collection_Table) load 'GOLF ESTATE - Total' as Collection autogenerate 1;

1.2. Make the Expression conditional for this line
if(only({1}Collection) = 'GOLF ESTATE - Total',

   sum({<Collection={'Golf Estate - (Fairway West)','Golf Estate II & Polo Suites'}>}total Value),

   sum(Value))

Hint: Using default on numers tab you can even do %-lines using the num() function.

2.1 Create a link table looking like this (eg. maintained in Excel):
Format    Sort    Collection_Group                    Collection
1             1         Golf Estate (Fairway West)     Golf Estate (Fairway West)

1             2         Golf Estate II & Polo Suites     Golf Estate II & Polo Suites

2             3         GOLF ESTATE - Total            Golf Estate (Fairway West)

2             3         GOLF ESTATE - Total            Golf Estate II & Polo Suites

1             4         Merlin                                      Merlin

...
2.2 Use the Collection_Group as dimension of your chart

2.3 Use the "+" Symbol in front of the dimension an put this format expression

only({1}if(format=2,'<b>'))

2.4 Use the Sort-Tab marking expression to sort via this expression

only({1}Sort)

2.5 Use step 2.3 in the expressions as well.

Hope this is of any help to you
Tobias

[umartareen]

Hi Tobias,

Great, works perfect ! Thank You.

Umar