4 Replies Latest reply: Apr 25, 2013 4:43 PM by JOSE MARIA TOS RSS

    Using aggr and total with date ranges

      I am trying to create a chart with the number of new customers that we have sold to this week. Where new is defined as anyone who has not bought from us in a set timeframe. (Eg last 3 years)

       

      Idea of chart would be:

      Weekend# of  new customers
      4/21/201350
      4/14/201327
      4/07/201313
      3/31/201368
      3/24/201344

       

      The underlying table basically looks like this:

       

      OrderId, AccountId, Date, Amount

       

       

      I have gotten close with this:

       

      Dimension: Weekend(Date)

       

      Expression: sum(aggr(if(sum(Amount) = sum(TOTAL<AccountId> Amount),1,0),Date,AccountId))

       

      The problem is that the Total is not date bound by the Order Date, and will pick up both older orders than we want and more recent orders which also want to be excluded.

       

      Any help or direction would be much appreciated

       

      Nick Clift

      DVL Inc

        • Re: Using aggr and total with date ranges
          Alan Farrell

          Hi Nick,

           

          try the following,

           

          Create a Variable and then paste this into it:

           

          Date = {">=$(=DATE(ADDMONTHS(FLOOR(MONTHSTART(MAX(TOTAL Date))), -35)))<=$(=DATE(FLOOR(MONTHEND(MAX(TOTAL Date)))))"}

           

          Call the variable

           

                 last.3.years

           

          Then use the following Expression

           

                 SUM({<$(last.3.years)>}Amount)

           

          Hope that helps

            • Re: Using aggr and total with date ranges

              Thanks for the reply Alan and it is a little helpful.  The problem I am still running into is that it is not relative to the particular row in the chart.  More explictly MAX(TOTAL Date) always resolves to the same thing.

               

              What I am trying to find is something I can put in a sum( ) expression that will resolve to something different depending on the row.

               

              Using the previous table as an example

               

              Weekend# of  new customers
              4/21/201350 <- sum() Calculated on the date range 4/21/2013  4/22/2010
              4/14/201327 <- sum() Calculated on the date range 4/14/2013  4/14/2010
              4/07/201313 <- sum() Calculated on the date range 4/07/2013  4/07/2010
              3/31/201368 <- sum() Calculated on the date range 3/31/2013  3/31/2010
              3/24/201344 <- sum() Calculated on the date range 3/24/2013  3/24/2010

               

              I am not entirely certain it is possible to do via an expression.

               

              Nick