Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

1 Reply
MVP
MVP

Re: Calculating a MIN Date greather than MIN Date of another expression

Maybe like

Date(

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

)

This and your short date calculation seem to be dependent on your table being sorted chronological.

Community Browser