I am brainstorming to get the desired output for the below requirement.
Transaction Date
Deal
Prev_Deal
Sales Margin
Derived Field
Status
30-Jul-16
12345
0
1000
1000
Dead
1-Aug-16
67890
12345
2000
1000
Dead
2-Aug-16
67891
67890
4000
3000
Dead
3-Aug-16
67892
67891
5000
4000
Dead
1-Sep-16
67889
67892
7000
2000
Live
I need to calculate the derived field based on Month
1. As of 30-Jul-2016 Transaction date I have reported 1000 Sales Margin. (As of 30-Jul-2016 Status is also only)
2. On 1-Aug-2016 67890 is inserted, deal 12345 is parent. Since for Jul I have reported 1000 for Aug I have to report 2000-10000=1000 is my derived field. For deal 67891,same as 67890. At the end of Aug I have reported 4000
3. On 01-Sep-16 since I have reported 4000, now I should report 7000-4000.
Please advice how I derive the logic. All the Deal and Prev_Deal are interlinked and are in one package.
from my understanding there is not a one-step way to accomplish your requirement in the load script. I would suggest looking at flagging the data so that you can aggregate the number over each deal path and per month within the deal path.
In the attached example I have used the Hierarchy load prefix to group the deals over the first deal in the chain of deals. The date value then needs to be reformatted to a month to allow the second level of grouping. In this example I used Month Start to find what month each date belongs to.
With these two grouping you will have to find a way to aggregate the derived value as you expect. I am not sure exactly what the best approach for the aggregation would be.
Thanks a lot for quick turn around. Logic looks good, however when I reload Derive column is disappearing. Did I miss some thing ? I mean in script the couldn't see Derive column
sorry, I accidentally left the derive field in the sample, after trying to find a quick way to add it. As I mentioned above I am not able to find a simple solution to your required logic.
The main complexity I struggled with is that you need to aggregate per month within one deal path, and then carry over result to next month. I did not find a simple way to do that. My assumption is that you might need to flag the aggregated intervals and then aggregate the intervals. I think you mentioned trying to do this thought FOR loops or similar, have you tried that approach?
