topic Re: Sum vs Aggr() in QlikView

Sum vs Aggr()

agigliotti — Fri, 12 Jul 2013 10:17:08 GMT

Hello All,

I have the below data:

fathercode itemcode color sales

B/21105 B/21105 - 04B 04B 1200

B/21105 B/21105 - 093 093 2000

In the database we have other itemcode with the same color.

In the presentation, the user have to select one fathercode (ex. B/21105) and he'd see the table below:

itemcode color sales total color sales

B/21105 - 04B 04B 1200 80000 (total sales with color = 04B)

B/21105 - 093 093 2000 95000 (total sales with color = 093)

with the column "total color sales" like the sum of all itemcode with that color, also I have to see only the itemcode within the current selection (fathercode = B/21105).

I tried to use SUM( TOTAL or Aggr() function but with no luck.

How can I achieve it ?

Someone can help me ?

Many thanks in advance.

Best regards

Andrea

Re: Sum vs Aggr()

Gysbert_Wassenaar — Fri, 12 Jul 2013 11:36:01 GMT

sum(total <color> sales) should give you the total for the color. Replace color and sales with the exact case sensitive field names of your data model.

Re: Sum vs Aggr()

agigliotti — Fri, 12 Jul 2013 12:22:42 GMT

that is giving me the same values as "sales" column maybe because the current selection is fathercode = B/21105

Re: Sum vs Aggr()

swuehl — Fri, 12 Jul 2013 13:19:41 GMT

So having a chart with dimensions item_code and color and expression sum(sales) you additionally want to sum the sales per color, ignoring your selection and the item_code dimension, right?

I would start with something like

=sum({1} total<color> sales))

which should show you the correct color sales, but will introduce new lines with item_codes that are not related to your fathercode selection. You can remove this lines using something like

=if(sum(sales),sum({1} total<color> sales))

or if you in fact need to show the sales share per color, just use:

=sum(sales) / sum({1} total<color> sales)

Instead of set identifier {1} you can use different set expression to only ignore fathercode selection:

=sum(sales) / sum({<fathercode=>} total<color> sales)

Re: Sum vs Aggr()

agigliotti — Fri, 12 Jul 2013 14:15:53 GMT

Yes you well understood my needs!

but if you use {1} or {<fathercode = >} I get all itemcode in dimension values even those are not related to the current selection!

That's problem I faced!

also I don't understand your expression : =if(sum(sales),sum({1} total<color> sales))

What it means ?

Re: Sum vs Aggr()

swuehl — Fri, 12 Jul 2013 14:31:27 GMT

Hm, that should limit your chart dimension values shown to the ones related to your selection.

Check attached.

edit: updated qvw

Re: Sum vs Aggr()

agigliotti — Fri, 12 Jul 2013 15:15:30 GMT

I see but using sum({1} total<color> sales) I think it'll ignore the current selection and if I select for example one year how would be the behavior ?

In that scenario I need to retrieve the color total sales for the year selected.

Re: Sum vs Aggr()

swuehl — Fri, 12 Jul 2013 15:21:28 GMT

That's why I suggested another expression to only ignore selection in fathercode:

"Instead of set identifier {1} you can use different set expression to only ignore fathercode selection:

=sum(sales) / sum({<fathercode=>} total<color> sales)

Thus year selection will be taken into account.

Re: Sum vs Aggr()

agigliotti — Fri, 12 Jul 2013 15:32:13 GMT

ok it seems to works but I still not understand the conditional expression if(sum(sales), then ....

sum(sales) returns true or false ? and why ?

Re: Sum vs Aggr()

swuehl — Fri, 12 Jul 2013 15:41:33 GMT

Ok, might need an explanation.

sum(sales) does not return a boolean, TRUE or FALSE, but a number (sum of your sales values).

But QV interpretes the number zero ( 0 ) as FALSE, any other number as TRUE.

Just a note, if you use QV functions true() and false(), they return dual values, with a text representation, boolean values True and False (e.g. in a textbox), but also with a number representation, zero for False and -1 for True.

If you want to make things more clear, you can also write above like

=if(sum(sales) <> 0, sum({1} total<color> sales))

Hope this helps,

Stefan