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.
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.
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.