Sum vs Aggr()

[agigliotti]

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

[Gysbert_Wassenaar]

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.

[agigliotti]

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

[swuehl]

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)

[agigliotti]

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 ?

[swuehl]

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

Check attached.

edit: updated qvw

[agigliotti]

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.

[swuehl]

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.

[agigliotti]

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 ?

[swuehl · Accepted solution]

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