6 Replies Latest reply: Jan 6, 2015 5:05 AM by Kamiel Rajaram RSS

    Help with AGGR

    Abhinava Chandra

      Hi,

       

      I have a requirement where I have a list of distributors and their sales with YearQtrs. I want to display only those resellers who have sales in the currently selected quarter but not in the previous 8 quarters. I have resellers with negative sales which I want to filter out. Therefore If a distributor has a positive sales in any one of the previous 8 quarters I want to exclude that as well as the one with negative sales.

       

      I am attaching a sample data.

       

       

      Thanks in advance.

      Abhinava

        • Re: Help with AGGR
          Ruben Marin

          Hi Abhinava, I did it without aggr, in Script I added a Period Field:

          LOAD [Analysis Year Quarter],

               MakeDate(Left([Analysis Year Quarter], 4), Right([Analysis Year Quarter],1)*3) as Period,

               WW_Reseller_Parent,

               Sales

          FROM

          [.\Community Aggr.xlsx]

          (ooxml, embedded labels, table is CH26_20150105_162714);

           

          and then a simple table with WW_Reseller_Parent as Dimension and an expression like:

          If(Sales, //Or "If(Sales>0" if you want to get only the ones with positive sales in selected quarter

          If(Count({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, Sales={">0"}, [Analysis Year Quarter]>} DISTINCT [Analysis Year Quarter])=1, Sum(Sales))

          )

           

          Hope this helps

            • Re: Help with AGGR
              Abhinava Chandra

              Hi Ruben,

               

              Thanks for the reply. I have tried this method but what I want differs from this. I want the sales to be aggregated for each distributor at YearQtr level and then filter out the negative bookings, not each individual rows of negative bookings.

               

              For example,

              2012 Q3     Dist1     56.6

              2012 Q3     Dist1     -24.5

              2012 Q4     Dist 1     -6.6

               

              Then I want the aggregated sum to be considered for each Distributor and YearQtr. thus for my example for 2012 Q3 Dist1 should come up with 22.1 and for 2012 Q4 the -6.6 should be neglected

               

              I hope this clears the confusion.

               

              Thanks,

              Abhinava

                • Re: Help with AGGR
                  Ruben Marin

                  Sorry I'm not sure to understand it right, you want something like?:

                  If(Sum(Sales)>0,

                  If(Sum({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, [Analysis Year Quarter]>}

                       Aggr(If(Sum({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, [Analysis Year Quarter]>}Sales)<>0, 1, 0), [Analysis Year Quarter], WW_Reseller_Parent))=1, Sum(Sales))

                  )

                   

                  It takes the customers with positive sales, and with no sales in the previous 8 quarters of the selected.

              • Re: Help with AGGR
                Kamiel Rajaram

                Hello,

                If based on selections, have you tried something like the below in your expression.

                Aggr(Sum(If(Sales > 0,Sales, 0)),WW_Reseller_Parent,[Analysis Year Quarter])

                 

                Regards

                Kamiel

                  • Re: Help with AGGR
                    Abhinava Chandra

                    Hi Kamiel,

                     

                    I want the following filter. First I want to get the aggregated bookings for the previous 8 qtrs. Assuming Period is autonumber of YearQtr.

                    Aggr(Sum({<Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}Sales),WW_Reseller_Parent,[Analysis Year Quarter])

                    Then I want to take only the zero sales in this aggregated table.


                    I am not able to compare each individual row of this aggregated table in memory to zero.


                    Is there any way I can do that?


                    Thanks,

                    Abhinava

                      • Re: Help with AGGR
                        Kamiel Rajaram

                        Hi Abhinava,

                         

                        Sorry, but I am not to clear on what you want to achieve with comparing to zero. Hope this helps though.

                         

                        Aggr(Sum({<WW_Reseller_Parent = , Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}if(Sales > 0,Sales) ),WW_Reseller_Parent,[Analysis Year Quarter])

                         

                         

                        Aggr(Sum({<WW_Reseller_Parent = , Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}if(Sales <= 0,Sales) ),WW_Reseller_Parent,[Analysis Year Quarter])

                         

                         

                         

                        Regards

                         

                         

                         

                        Kamiel