9 Replies Latest reply: Apr 10, 2018 3:35 AM by Steve Br. RSS

    Dates in set analysis

    Steve Br.

      HI all,

       

      I have an issue with a measure that I am trying to set up.

       

      The data has three different date fields handled by the autoCalendar:

       

      EndDate: when a contract with an external company expires

      OrderDate: when an order is placed

      InvoiceDate: when the invoice is paid

       

      So I am trying to find out if an order has been placed after the contract expiration date (may be one or hundreds of orders) and if so, what is the amount paid for the period after the contract has expired. So I am going like:

       

      IF(MAX([OrderDate.autoCalendar.Date]) > [EndDate.autoCalendar.Date],
      SUM({<[InvoiceDate.autoCalendar.Date]={">=[EndDate.autoCalendar.Date]"}>}InvoicedAmount))
      
      

       

      This returns zeros when placed in a straight table with contract as a dimension. The first line of the expression works fine, the problem comes from the set analysis. How should I state that only the InvoicedAmount for the InvoiceDates after the EndDate should be summed?


      Thank you!