7 Replies Latest reply: Feb 10, 2017 3:30 PM by Marcelo Mtanous RSS

    Multiple conditions in aggr(sum(if(x AND y)... help

      Hi, need some help - someone up a saturday?
      I want to add a condition to the following existing calc:
      aggr(sum(qty_a),label) / aggr(sum(qty_b),label)

      I only want to sum if the rows match my variable that is:
      CurrentMonth = =num(month(Today()))
      Formula something like:
      =aggr(sum(if(iMonth=Currentmonth,qty_a,0),label) / aggr(sum(if(iMonth=CurrentMonth,qty_b,0),label)
      *Where iMonth is the month-number from the data rows.

      Data sample:
      iMonth, Label, qty_a, qty_b
      1, ALFA, 5, 10
      2, ALFA, 0, 5

      Any ideas?
        • Multiple conditions in aggr(sum(if(x AND y)... help
          Stefan Wühl

          Hi,

           

          not sure if I understood correctly...

          You are using a (table) chart, with dimension iMonth, correct?

           

          Maybe you want to try this (I just added iMonth to your aggr-dimensions and one missing bracket):

           

          =aggr(sum(if(iMonth=CurrentMonth,qty_a,0)),Label,iMonth)

          / aggr(sum(if(iMonth=CurrentMonth,qty_b,0)),Label,iMonth)

           

          Regards,

          Stefan

            • Multiple conditions in aggr(sum(if(x AND y)... help

              Hi Swuehl,
              Using a pivot, and the data is loaded from a database.

              The goal is to compare current vs last month to get a trend... (in the end).

               

              Your suggestion didnt work, but I get no errors eithers.


              LOAD * INLINE
              [iMonth, Label, qty_a, qty_b
              1, A, 2, 10
              1, A, 2, 5
              2, A, 1, 20
              2, A, 3, 10
              ];

               

              //Or a variable...

              LOAD * INLINE
              [CurrentMonth
              2
              ];

                • Multiple conditions in aggr(sum(if(x AND y)... help
                  Sunil Chauhan

                  vlastMonth=CurrentMonth-1

                  aggr(sum(if(iMonth=$(CurrentMonth),qty_a,0)),Label,iMonth)

                  / aggr(sum(if(iMonth=$(vlastMonth),qty_b,0)),Label,iMonth)

                   

                  hope this help

                  • Re: Multiple conditions in aggr(sum(if(x AND y)... help
                    Stefan Wühl

                    Hi tobbeandersson,

                     

                    I put my exact above expression in a pivot and it seems to work (I attached my sample, please have a look).

                    (I duplicated your data up to december, because current month is September, so I assume we want to have data for todays month?)

                     

                    If you want to compare current vs last month in a table with iMonth dimension, you may wanna take a look into the chart inter record function, above() for example.

                     

                    I created a second table as example for that.

                     

                    Regards,

                    Stefan

                      • Re: Multiple conditions in aggr(sum(if(x AND y)... help
                        Marcelo Mtanous

                        Hi Stefan swuehl

                         

                        I am struggling with a similar topic. I would like to use two conditions within the aggr function to calculate how many clients buy from the two products. See below my sample table;

                         

                          

                        AccountProduct1Product2
                        Client110
                        Client2200
                        Client310050
                        Client400
                        Client5030
                        Client6400
                        Client700
                        Client8200
                        Client93030
                        Client1000
                        Client11100

                         

                        The expression I am using for counting the clients that buy from product1 is the below;

                         

                        Count(distinct if(aggr(sum({$<[Product Line] ={'Product1'}>} Run_Rate__c), Account) > 0, Account))

                         

                        How can I add into the aggr function the second product, Product2, so I can get only the clients that buy from both. In this case, 2 will be the answer.

                         

                        Appreciate very much your help