2 Replies Latest reply: Jul 29, 2013 5:05 AM by Ralph Graham RSS

    Complicated Set analysis

      Hello,

       

      I have a particular expression that I need to use but it is proving to be quite complicated (see below current expression). Basically what I want to do is sum the amount of packs based on a number of criteria using a two date variables. In orange I only want want to sum the data which is between the vMaxDate and 21 days before it of the ReportDate, then in green I only want to sum the data which is between 161 and 449 days based on the vMaxDate and vMinDate of the Report Date, and once these two conditions have been met I want to sum the packs between the vMinDate and vMaxDate of the report.

       

      The below expression works fine when I have the Customer, Product and ReportDate as dimensions but essentially I want a summary table to only have Product as the dimension (I know that aggr needs to be used however I'm having no luck filtering out based on the previous if statements).

       

      if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}ReportDate >max(date(vMaxDate)-21),

      if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) >161 and max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) <449),

      sum({<ReportDate={">$(=date(vMinDate)) <=$(=date(vMaxDate))"}>}Packs)))

       

      RawData:

       

      CustomerProductReportDateDaysPacks
      CustomerAA20/07/20132354000
      CustomerAA18/07/20135005000
      CustomerAA06/06/20132006000
      CustomerBA16/07/20133502000
      CustomerBA13/05/20135007000
      CustomerCB21/07/2013600400
      CustomerCB13/07/20133005000

       

       

      OutputTable: (assuming vMaxDate is 26/07/2013 and vMinDate is 01/05/2013)

       

      ProductPacksReportDate
      A600020/07/2013
      B500021/07/2013

       

      From the RawData table rows 3 and 5 do not meet the criteria of being inside 21 days of the vMaxDate so should not be considered, whilst rows 2 and 6 do not meet the criteria of being between 161 and 449 days, which leaves row 1, row 4 and row 7.


      Thanks,

       

      Ralph

        • Re: Complicated Set analysis
          Tammy Milsom

          Try, not the most efficient with the constant use of if statements.

           

          sum

          (AGGR(

          if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}ReportDate) >max(date(vMaxDate)-21),

          if(max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) >161 and max({<ReportDate={">$(=date(vMinDate)) <= $(=date(vMaxDate))"}>}Days) <449,

          sum({<ReportDate={">$(=date(vMinDate)) <=$(=date(vMaxDate))"}>}Days))),ReportDate))