topic Re: Calculating total in a straight table in QlikView

Calculating total in a straight table

dafnis14 — Mon, 12 Oct 2015 12:29:59 GMT

Hi,

I'm trying to achieve the following:

I have a metric: Target = Quantity - Diff

In a straight table, I would like to display one row for a country, which would be identical

to the Target displayed in the top table (Straight Table - with Branches), which is based on the sum of rows

But, I receive total which is calculated as the difference of the total sums instead.

How can this calculation be achieved?

Straight Table - with Branches

Branch	Quantity	Diff	Target
	5311	2903	4608
A	1739	274	1465
B	1595	252	1343
C	1977	177	1800
Straight Table - with Branches - Wanted
Country	Quantity	Diff	Target
France	5311	2903	4608
Straight Table - with Branches - Received
Country	Quantity	Diff	Target
France	5311	2903	2408

Thanks!

Re: Calculating total in a straight table

Anonymous — Mon, 12 Oct 2015 12:38:58 GMT

Hi Dafnis ,

Can You please attach a sample app or explain elaborately please.

thanks ,

Bunny

Re: Calculating total in a straight table

Mon, 12 Oct 2015 13:07:04 GMT

In the straight tablw with branches, the total, based on your statement of Target = Quantity - Diff- diff is not correct (5311 - 2903 does not equal 4608)

Do not understrand straight table wanted - it has the same totals as the first example which do not appear to add correctly

In the 3rd example ...-Received, the math is correct based on your statement of Target = Quantity - Diff

(5311-2903 = 2408)

it appears the totals of the quanity and target are the sum of the details, but that does not appear to be the case for diff

branches a,,b, c in france?

can it be a pivot table with country and branch - when all collapsed it would like like a straight table but you would be able to get branch level numbers

Re: Calculating total in a straight table

miguelbraga — Mon, 12 Oct 2015 13:07:59 GMT

The Total Diff Value in the first table isn't right! Can you please tell us what's the diference between Wanted and Received?

Re: Calculating total in a straight table

Mon, 12 Oct 2015 13:08:30 GMT

In the straight tablw with branches, the total, based on your statement of Target = Quantity - Diff- diff is not correct (5311 - 2903 does not equal 4608)

Do not understrand straight table wanted - it has the same totals as the first example which do not appear to add correctly

In the 3rd example ...-Received, the math is correct based on your statement of Target = Quantity - Diff

(5311-2903 = 2408)

it appears the totals of the quanity and target are the sum of the details, but that does not appear to be the case for diff

branches a,,b, c in france?

can it be a pivot table with country and branch - when all collapsed it would like like a straight table but you would be able to get branch level numbers

Re: Calculating total in a straight table

Qrishna — Mon, 12 Oct 2015 13:09:14 GMT

This way?

Re: Calculating total in a straight table

dafnis14 — Mon, 12 Oct 2015 13:40:28 GMT

Hi All,

Thank you all for your kind help.

Please note that i had to simulate a very simplified example based on complex expressions.

The wrong Diff total in the first table was received because the expression's Total Mode

was defined as the Expression Total.

Staright Table - with Branches

Branch	Quantity	Diff	Target
	5311	2903	4608
A	1739	274	1465
B	1595	252	1343
C	1977	177	1800

When changing to Sum of rows, it would display correct.

But, the desired metric is the Target. When I changed to Pivot, the totals still displayed wrong.

Staright Table - with Branches - Received
Country	Quantity	Diff	Target
France	5311	2903	2408

I will try to generate an example app to clarify.

Re: Calculating total in a straight table

Mon, 12 Oct 2015 13:48:29 GMT

Still not sure I undersatnd - in your last example

the target of 2408 is correct based on the data (5311-2903)?

the straight table with branches in your last example has the same total numbers but the detail for diff do not add up to the total

You first examples says you want a target of 4608 - is that correct? If that is the case, the issue is the total of the diff column is incorrect but the detail is correct. By subtracting the detail diff from quantioty, the correct target is calcualted -diff total should then be 703

vcan you send example with correct diff numbers?

Re: Calculating total in a straight table

Mon, 12 Oct 2015 14:13:01 GMT

I believe, your simulation have different set off results on Country Level.

Please post sample data.

Re: Calculating total in a straight table

dafnis14 — Mon, 12 Oct 2015 17:13:34 GMT

Hi Adam,

In a straight table, where the country and country are dimensions, the total target value is

What we'd like it to be:

Quant.	Diff	Target
5311	703	₪ 4,608
1739	274	₪ 1,465
1595	252	₪ 1,343
1977	177	₪ 1,800

When I change the same table to pivot, the Diff total is wrong, as you mentioned, and so is the target:

Quant.	Diff	Target
5311	2903	₪ 2,408
1739	274	₪ 1,465
1595	252	₪ 1,343
1977	177	₪ 1,800

What am i missing here?

Thanks!

Re: Calculating total in a straight table

Digvijay_Singh — Tue, 13 Oct 2015 04:48:26 GMT

Can you share the expression used for Diff and target, I think that is making all the difference.

Re: Calculating total in a straight table

jonathandienst — Tue, 13 Oct 2015 05:12:49 GMT

>>The wrong Diff total in the first table was received because he expression's Total Mode was defined as the expression Total.

>> When changing to Sum of rows, it would display correct.

A pivot table always calculates the expression total and not a sum of rows. To get sum of rows in a pivot, you need to use a Sum(Aggr(.....)) expression, using this syntax:

=Sum(Aggr( ... your expression here ..., <chart dimension1>, <chart dimension 2>, ...)

The chart dimensions are ALL the fields used as chart dimensions and they must all be included in the Aggr()

dimensions. Example:

=Sum(Aggr(Sum(Amount), Country, Branch))

Re: Calculating total in a straight table

dafnis14 — Tue, 13 Oct 2015 08:28:49 GMT

Thanks Jonathan!