Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti
Partner - Champion
Partner - Champion

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
swuehl
MVP
MVP

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

View solution in original post

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


talk is cheap, supply exceeds demand
agigliotti
Partner - Champion
Partner - Champion
Author

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

swuehl
MVP
MVP

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
Partner - Champion
Partner - Champion
Author

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

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

Check attached.

edit: updated qvw

agigliotti
Partner - Champion
Partner - Champion
Author

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

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
Partner - Champion
Partner - Champion
Author

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

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