topic Regarding Total Sum in a Straight table in QlikView

Regarding Total Sum in a Straight table

Fri, 08 Jan 2016 13:37:41 GMT

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

Re: Regarding Total Sum in a Straight table

Anonymous — Fri, 08 Jan 2016 13:48:04 GMT

Check this...

Like this?

Re: Regarding Total Sum in a Straight table

Anonymous — Fri, 08 Jan 2016 13:51:05 GMT

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

See the attachment..

Re: Regarding Total Sum in a Straight table

NareshGuntur — Fri, 08 Jan 2016 13:57:09 GMT

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

Re: Regarding Total Sum in a Straight table

Fri, 08 Jan 2016 14:07:12 GMT

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!

Re: Regarding Total Sum in a Straight table

NareshGuntur — Fri, 08 Jan 2016 16:04:08 GMT

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.

Re: Regarding Total Sum in a Straight table

sfatoux72 — Fri, 08 Jan 2016 16:40:31 GMT

Hi,

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

Re: Regarding Total Sum in a Straight table

settu_periasamy — Fri, 08 Jan 2016 16:56:59 GMT

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

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

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