Complicated OLAP table using variables, pulls and date calculations

Hi Team,

I have a rather complex OLAP table that I am looking to build and need your help. Its a two step issue that I am hoping that someone has some guidance.

Part 1.
We have a table in the back end that contains pmt term information. We have ranked the payments terms from 1 best to 30 worst. I have also created a drop down variable for the user to be able to select 1, 2, or 3.
Essential, what i need to happen is, based on the current payment term that we have for the invoice, if a user selects 1, in the variable, then the next best pmt term will be populated in that column. if they select 2, then it would skip to the 2nd better pmt term that is on that list. so on and so on.

Can you please advise if you can help me solve this issue.

Part 2:
Given that the first part can be done.
The next part would be to calculate the days difference between the net due date of the original pmt term and the new payment term net due date. The original payment net due date we already pull into the model.
The complication that i see here, is how i would calculate the net due date of the new pmt term that is populated as this will not be static and can be based on several different aspects. i.e based on invoice receipt date, invoice posted date. etc.

please let me know if this is something that can be done, and if so, how would i go about the best way of achieving it.

P.s. not sure if i explained myself thoroughly, so if you have any questions, be happy to discuss.

Thanks a million.

Hi All,

I have been trying to find a workaround to the 2nd part of my question. I have made some changes to the backend file that i think would make things easier. However, I still need your assistance as i cannot seem to make the various formulas work.

image

I would like to include a column that would add the days in column F to the posted date.
I have tried various formulas including the below to no avail.

ADD_DAYS(ROUND MONTH((“CASES”.“PostingDate”)) * (“PaymentTermRank”.“Add Days”))

Essentially, based on the payment terms for that invoice, i would like to add the days based in this new column to the posting day.

Please let me know if this is possible and if you can help.

Thanks!
Anna