topic Re: Subtracting Row Totals in QlikView

Subtracting Row Totals

umartareen — Tue, 05 Aug 2014 09:31:44 GMT

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

Re: Subtracting Row Totals

datanibbler — Tue, 05 Aug 2014 09:48:25 GMT

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

Re: Subtracting Row Totals

Anonymous — Tue, 05 Aug 2014 10:03:00 GMT

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))

Re: Subtracting Row Totals

umartareen — Tue, 05 Aug 2014 11:03:25 GMT

Thank you DataNibbler and Satyadev Jaiswal !

Re: Subtracting Row Totals

tobias_klett — Tue, 05 Aug 2014 12:12:04 GMT

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

Re: Subtracting Row Totals

umartareen — Thu, 07 Aug 2014 06:37:11 GMT

Hi Tobias,

Great, works perfect ! Thank You.

Umar