Regarding Total Sum in a Straight table

[#1]

Hello Friends,

I appreciate your time!

I have a query on how the grant total works in a straight table, could you have a look at the attached application and please let me know.

Thanks,

Kiruthi

[Anonymous]

Check this...

Like this?

[Anonymous]

Properties -> Expression -> Total Mode -> Tick Sum of Row option

See the attachment..

[NareshGuntur]

Hi Kiruthi,

Expression1:

Total Amount: 300

Expression used:   =aggr(sum(Amount),ProductGroup)

You need to enclose the aggr function with one more aggregation function. If not it doesn't make any difference.

If you change the expression to this:    =Sum(aggr(sum(Amount),ProductGroup))

It will give you 600 as Total.

Expression2:

Total Amount: 500

Expression used:    sum(Amount)

When the link happens between Fact table and Dimension table, your "Product" field in Dimension table is not unique.

"Eco Milk" is repeating. So, the Amount 100 will go to both "DairyProducts" and "EcoProducts" which actually makes the total as 600.

But the total you see is only 500 as the original total if you take Amount field alone is only 500.

Hope it is clear

Cheers,

Naresh

[#5]

Dear Friends,

Thanks so much for all your response and I sincerely appreciate your time. I am not sure I am right or wrong:-(

My question  from my attached application is that both the expression denotes the samelogic for the given dimension, so how the virtual table get calculated behind to give a correct total sum of 500 for expression sum(Amount) but it is wrong in the case of expression aggr(sum(Amount), Product group)

Hope you got it!

[NareshGuntur]

As I mentioned in the above post, correct way of using aggr in the expression which you are trying to get is

Sum(aggr(sum(Amount), Product group)) rather than aggr(sum(Amount), Product group).

aggr(sum(Amount), Product group) will first add the amount according to Product Group and create a virtual table and leave as it is. So, even your total stopped at 300.

Sum(Amount) is adding up the total Amount accordingly.

[sfatoux72]

Hi,

it's just because your Product "Eco Milk" is in two Product Group "DairyProducts" and "EcoProducts"

[settu_periasamy]

Hi,

I read it here..

Pitfalls of the Aggr function

"Aggregation will produce the virtual table exactly one row per distinct Combination"

The below points are based on my Understanding..

1.

In this case, you have used  the expression :  aggr(sum(Amount), Product group)

See the below Virtual Table, Amount is 300 and 300... so the distinct value also 300

If you change your Amount value as 100, 50, 200... in the total bar you will get the Null Value

Normally, we do like this...The below one will give result as 600

Sum(aggr(sum(Amount), Product group))

2.

Sum(Amount)

You have two tables..

If you do the Left join, you will get result as 600..

If no, You need to do above mentioned expression : Sum(aggr(sum(Amount), Product group))

Or Enable the Total Mode as 'Sum' in Expression Tab

Hope this will help a Bit..