9 Replies Latest reply: Jul 12, 2013 11:41 AM by Stefan Wühl RSS

    Sum vs Aggr()

    Andrea Gigliotti

      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

          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()
              Andrea Gigliotti

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

                • Re: Sum vs Aggr()
                  Stefan Wühl

                  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()
                      Andrea Gigliotti

                      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()
                          Stefan Wühl

                          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()
                              Andrea Gigliotti

                              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()
                                  Stefan Wühl

                                  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()
                                      Andrea Gigliotti

                                      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()
                                          Stefan Wühl

                                          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