1 Reply Latest reply: Dec 21, 2015 5:46 PM by Stefan Wühl RSS

    Calculating a MIN Date greather than MIN Date of another expression

      I have supply data that I need to calculate specific dates about. Net is the running total of picks and replenishment of parts, and Supply is the current Supply available for that part and contract. Total Supply on each date is determined by Supply + Net.

       

      I am using the following expression to determine my "Short Date". Short Date is the first date that we go below 0 in Total Supply.

       

      Short Date

      MIN(if(if(isnull(Net),0,Net)+Supply<0,DATE(Date)))

       

      I now want to calculate the Next Delivery Date. This would be the first date greater than the Short Date that has a PO Qty >0. This is the formula I tried, but it evaluates to Null for all values:

       

      Next Delivery Date

      Min(if(PO_Qty>0 AND Date > MIN(if(if(isnull(Net),0,Net)+Supply<0,DATE(Date))),Date))

      or

      Min(if(PO_Qty>0 AND Date > column(1),Date))

       

      I have also tried wrapping the expression in a $(), but this evaluates the expression globally instead of on the line item level of the pivot chart.


      Min(if(PO_Qty>0 AND Date > $(=MIN(if(if(isnull(Net),0,Net)+Supply<0,DATE(Date)))),Date))


      For instance, Part A, Contract_ID 28649 should have a Next Delivery Date of 12/22/2015, since this is the first date after the short date that has a PO_QTY > 0.


      Attached qvw sample.