Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

agigliotti
Honored Contributor II

Sum vs Aggr()

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum vs Aggr()

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

9 Replies

Re: Sum vs Aggr()

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.


talk is cheap, supply exceeds demand
agigliotti
Honored Contributor II

Re: Sum vs Aggr()

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

MVP
MVP

Re: Sum vs Aggr()

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
Honored Contributor II

Re: Sum vs Aggr()

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 ?

MVP
MVP

Re: Sum vs Aggr()

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

Check attached.

edit: updated qvw

agigliotti
Honored Contributor II

Re: Sum vs Aggr()

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.

MVP
MVP

Re: Sum vs Aggr()

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
Honored Contributor II

Re: Sum vs Aggr()

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 ?

MVP
MVP

Re: Sum vs Aggr()

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

Community Browser