10 Replies Latest reply: Jan 25, 2010 12:31 PM by Jean-Jacques Jesua RSS

    AGGR

    Jean-Jacques Jesua

      Hi

      I have some difficulies to understand well the AGGR( ) function, which is very useful as I saw in the forum

      Is anybody can give me some example to be more confortable with or some argument.

      Thanks again for your time.

       

      Jean-Jacques

       

       

       

        • AGGR
          Michael Solomovich

          Jean-Jacques,
          There could be many very different situation. Only one example:
          There are two tables:


          Master:
          SalesId,
          SalesDate,
          Amount,
          ...
          FROM...;
          Details:
          SalesId,
          Product,
          ...
          FROM ...;

          You can see that the relation is one (Master record) to many (Details records). You certainly can join into one table, but it will mutliply the number of records.
          In case you don't have to join them, the expression sum(Amount) will work fine. But if for any reason you have to join, the result will be incorrect. It can be corrected by using sum(aggr(only(Amount), SalesId)). No matte rhow many times the same SalesId appear in the table, it's Amoun twill be used only once in the sum().
          Apparently the example is not typical, just for illustration.

          • AGGR

            I use aggr function when i have to simulate where clause in a graph.

            For example: Display a graph with articles more than 5 of quantity.

            You have to perform an expression ( for example sum(ITEM_QTY) ) and decide if your article should taken or not. Tha aggr function let you to bring this decision.

            In the graph you have also to check that you don't want to display null value dimensions.

            See this example

            Hope this help

            Wink

             

              • Re. :Re: AGGR
                Jean-Jacques Jesua

                nice example Bud !

                it's better than to create a flag as i did.

                 

                regards

                JJ

                  • Re. :Re: AGGR

                    Hi Bud,

                    I am also facing problem in understanding about aggr. Can you please explain me how the below code behaves as i don't have any clue on how it works. For your reference i have attached the same document which i downloaded for understanding about aggr. Hope you can provide me the solution.

                     

                    count(if(aggr(sum(sales), year, company)>iDiffValue,1))


                      • Re. :Re: AGGR
                        Jean-Jacques Jesua

                        Hi,

                        Now I think AGGR is the best function in QV. With this, you don't need to aggregate the data outside of QV.

                        In the example you mentionned you have 3 steps ;:

                        1) for each year and for each company, you sum the sales => A

                        2) if A > threshold , you count 1 => B

                        3) a after you count the number of B

                        Hope to help you.

                         

                        Look at this example with 2 AGGR !

                        = Sum( Aggr( Max( aggr( sum(Close_Hour-Open_Hour)*24 , pcbur , Period) ) , pcbur) )

                        I just want to calculate the how many hours all the stores (pcbur) of an area (my dimension) are open by week.

                        regards.

                         

                        jj

                         

                         

                         

                          • Re. :Re: AGGR

                            Hi JJ,

                            I am totally confused here. Still i am not able to understand anything :-(. Could you please work it on in my document so that it is understandable for me. Also requesting you to explain it with some sample data also.

                              • Re. :Re: AGGR

                                Hi JJ,

                                I am waiting for your reply. Requirement is very urgent please help me out.

                                Regards,

                                Rikab

                                 

                                  • Re. :Re: AGGR
                                    Jean-Jacques Jesua

                                    Hi Rikak

                                    I don't understand what you don't understand in the formula

                                    count(if(aggr(sum(sales), year, company)>iDiffValue,1))


                                    Let's try step by step,

                                    First, you want a chart with year as dimension
                                    Second, you want to count hom many company each year has a sum of sale > IdiffValue (it's your threshold).
                                    To do this directly in the chart, you calculate aggr(sum(sales), year, company)
                                    => it's the calculation by year & by company of the sales

                                    Then you have to create a flag (1 or 0)
                                    as if( aggr(sum(sales), year, company) > iDiffValue, 1 ) = 1 if aggr(sum(sales), year, company) > iDiffValue
                                    = Missing otherwise

                                    Then you count the number of "1" . The count function deals with your dimension, the year in your case.

                                    Hope to help you

                                    JJ