Derivative rule: Add record for other time period

[TimVierhout]

Hi all,

We have a requirement, where in a datasource, we load revenue for the year. This is for the budget, and the workflow is a yearly workflow.

The file is on the customer/contract level and include the payment terms, which is the term in which the customer will pay. For the cash forecast, we want to leverage this to calculate when the cash is coming in related to the revenue.

What we want to do, is to create a derivative rule to create an additional payment entry in the upload, with a different time period based on the payment term. However, I am not able to change the time period in a derivative rule. Is this something that is not possible or is there a way to do this? The PAYMEMT record is generated, but the time is not adjusted. This is the syntax for the final derivative rule I am using:

T#[*]=2024M12:A#[EXT_PRICE]=PAYMENT

So the record is created, but the time is not adjusted unfortunately.

With kind regards,

Tim

[Henning]

Hi, it would be generally easier to calculate this in the cube after the import. I would try going that way.

Otherwise, you can use a business rule or complex expression in your derivative rule to pull the data you need from the corresponding table. If you go down that path, using Globals is highly recommended for performance. Otherwise, pulling the source data from other tables will (likely) degrade transformation performance notably. This can be done (without knowing the exact details of your requirements), but if you can calculate this in the cube as initially stated, that would be easier in my view.

[FredLucas]

Hi @TimVierhout,

Another option could be to set up two separate WF Import steps. One for the Revenue accounts (as-is) and another one for the Cash Forecast accounts that would have an expression determining the Time.

If this is going to be an automated load you could trigger both steps in parallel.

If it's file based and done manually, if required you could think of centralizing the process e.g.: a unique dashboard button that triggered both loads or having the process step of the first WF Import step triggering the execution of the second WF.

[TimVierhout]

Calculating in the cube would be a challenge, as the payment term is derived on a more granular level (Contract and Customer) then what we are storing in the cube.

We are evaluating 2 approaches:
1: A second import as Fred suggests.
2: Derivative rule to get the future time periods in a separate UD and calculate this in the cube.

But would be good to understand if this is a limitation in the import or there is something syntax wise we can do :).

With kind regards,

Tim Vierhout

[Henning]

I believe derivative rules apply to the table that is created during your import for each workflow step. That is why I think using parameters that point to a different table in a different workflow step will not work. 

Business rules, complex expressions or other creative solutions such as the one from @FredLucas seem necessary.