topic Re: Sum over aggr (in text object) in QlikView

Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 07:36:35 GMT

Dear Community,

i have a straight table with one dimension 'Group' and one expression '=SUM(Aggr(SUM(AMOUNT),GROUP))'

On the top are the totals by expression

The result looks like this :

Now i want to display the total 49398... directly inside a text object as a key performance indicator using the same expression

=SUM(Aggr(SUM(AMOUNT),GROUP)). But its not working as the number is not the same comparing with the straight table total

What i am making wrong? Please help

Re: Sum over aggr

alexandros17 — Wed, 23 Sep 2015 07:39:33 GMT

Aggr works with the dimensions inside the chart so try to add all the dimensions used in the chart to your aggr statement

Re: Sum over aggr

ali_hijazi — Wed, 23 Sep 2015 07:40:20 GMT

you need to ignore dimension by using total

ex:sum(total column)

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 07:41:27 GMT

Inside the straight table we have only one dimension called 'Group', so the total value should be the same inside the table and text object

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 07:44:56 GMT

I tried to use =SUM(Aggr(SUM(Total AMOUNT),GROUP)) but the value was also wrong

Re: Sum over aggr (in text object)

alexandros17 — Wed, 23 Sep 2015 07:46:05 GMT

Could you please try with the following expession in the chart and in the text box

'=SUM(Aggr(NODISTINCT SUM(AMOUNT),GROUP))'

Re: Sum over aggr (in text object)

ali_hijazi — Wed, 23 Sep 2015 07:47:10 GMT

may you please supply a sample QVW ?

Re: Sum over aggr (in text object)

jonathandienst — Wed, 23 Sep 2015 07:54:39 GMT

This expression in chart with Group as a dimension

=SUM(Aggr(SUM(AMOUNT),GROUP))

is just an inefficient way of saying

=SUM(AMOUNT)

The first expression is the correct way of expressing the sum and is the equivalent of the chart. If the totals are different, then you are using an expression total rather than a sum of rows, and the equivalent would then be the second expression. Unless you have some restrictions in the chart such as a calculated dimension that filters GROUP.

If that does not resolve the problem, then I suggest you post your qvw or a representative sample to see why it is behaving this way.

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 11:49:01 GMT

I have prepared an example qvw.file please see attached.

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 11:49:56 GMT

See attachement below

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 11:50:40 GMT

See attachement below

Re: Sum over aggr (in text object)

maxgro — Wed, 23 Sep 2015 11:53:53 GMT

maybe

=sum({$ <Group={"*"}>} Amount)

Re: Sum over aggr (in text object)

Anonymous — Wed, 23 Sep 2015 12:52:22 GMT

Hi Maxgro,

your solution is not interactive? For example when chossing group A the text object value does not equal with the correct total value of the sraight table. Any idea how to make it possible?

Re: Sum over aggr (in text object)

maxgro — Wed, 23 Sep 2015 13:01:04 GMT

maybe this

=sum({$ <Group*={"*"}>} Amount)

Re: Sum over aggr (in text object)

Anonymous — Thu, 24 Sep 2015 07:17:34 GMT

Best answer till now