Hi,
I have created a cash planning and would like to assign a part of the outstanding blanket orders to each month. I already have the function below to get the outstanding amount. But I would like to take only a part of that amount into consideration. Being the outstanding amount, divided by the number of days to the due date of the blanket order * 30. Would that be possible? With the NF function I can work with fields of the main table (sales line), but I cannot use fields from the linked "Sales Header" table? Thanks
=NL("Som";"Sales Line";"=NF(;""Outstanding Amount"")";"Document Type";"Blanket Order";"Type";"Item";"No.";"<>''";"Link=";"Sales Header";"Document Type";"=Document Type";"No.";"=Document No.";"Due Date";".."&D4)
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Steven,
No you can't use fields from the link table using an NF function. However, you can use an NL function inside a calculated field (or any Excel function for that matter). The calculated field formula is going to get passed through Excel to be evaluated so any valid Excel formula will work. I will take a crack at your formula but I'm not sure if I have the parenthesis right here:
=NL("Som";"Sales Line";"=NF(;""Outstanding Amount"")/(VALUE(NL(;""Sales Header"";""Due Date"";""No."";NF(;""Document No."");""Document Type"";NF(;""Document Type""))-VALUE(TODAY())))*30";"Document Type";"Blanket Order";"Type";"Item";"No.";"<>''";"Link=";"Sales Header";"Document Type";"=Document Type";"No.";"=Document No.";"Due Date";".."&D4)
My formula should give this result: (Outstanding amount / (due date - today)) * 30. However, I couldn't tell from your description if you wanted something more like this: Outstanding amount / ((due date - today) * 30). Anyway, you might have to add some parenthesis if you want it the other way, but it shouldn't be hard. Does that help?
Regards,
Hughes
Please sign in to leave a comment.