7 Replies Latest reply: Apr 23, 2018 11:04 AM by Sunny Talwar RSS

    SUM on the 2nd to last data point

    William Labarca

      Hello Everyone,

       

      I am having some difficulties with a SUM expression in which I am calculating overstock based on the date of the data pull.  I need Last Weeks overstock but the days of loads are not always a perfect 7 days.

       

      What I need is the second to last data point in this chart:

      Capture.JPG

      Currently my formula looks like this:

       

      SUM({$<[inv_age_rpt.inv_age.crte_dttm.autoCalendar.Date]={"$(=DATE(Today()-7))"}>}

      IF(([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst>0,

      (([inv_age_rpt.qt_91_180]+[inv_age_rpt.qt_181_270]+[inv_age_rpt.qt_271_365]+[inv_age_rpt.qt_365_plus]-[inv_age_rpt.sls_past_60])*prdct_cst),0))

       

      If today is 4/23, 7 days ago (4/16) is not a data point plotted on this chart, so I need to go back to the NEXT date which would be 4/13.

       

      Any assistance would be very helpful thank you!!