
Re: Calculating total in a straight table
Bunny V Oct 12, 2015 8:38 AM (in response to Dafnis X)Hi Dafnis ,
Can You please attach a sample app or explain elaborately please.
thanks ,
Bunny

Re: Calculating total in a straight table
Adam Krepistman Oct 12, 2015 9:07 AM (in response to Bunny V)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
(53112903 = 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
Miguel Braga Oct 12, 2015 9:07 AM (in response to Dafnis X)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
Adam Krepistman Oct 12, 2015 9:08 AM (in response to Dafnis X)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
(53112903 = 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
Krishna Nagulapally Oct 12, 2015 9:09 AM (in response to Dafnis X) 
Re: Calculating total in a straight table
Dafnis X Oct 12, 2015 9:40 AM (in response to Dafnis X)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
Adam Krepistman Oct 12, 2015 9:48 AM (in response to Dafnis X)Still not sure I undersatnd  in your last example
the target of 2408 is correct based on the data (53112903)?
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
Dafnis X Oct 12, 2015 1:13 PM (in response to Adam Krepistman)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 Oct 13, 2015 12:48 AM (in response to Dafnis X)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
Jonathan Dienst Oct 13, 2015 1:12 AM (in response to Dafnis X)>>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
Srikanth P Oct 12, 2015 10:13 AM (in response to Dafnis X)I believe, your simulation have different set off results on Country Level.
Please post sample data.


Re: Calculating total in a straight table
Dafnis X Oct 13, 2015 4:28 AM (in response to Dafnis X)Thanks Jonathan!