3 Replies Latest reply: Mar 16, 2016 3:14 AM by Stephane Chivet

# Split Transactions into Period

Hi,

I need to split transactions into different Period (month) depending of the transaction date the starting and ending date. This has to be done during the load of the data (will be used in multiple places).

Well the better way to explain is to show my input and what would be the output!

Input:

 Id Date of Transaction Starting Date Ending Date Amount 1 2016-01-12 2016-01-01 2017-01-01 12 000 2 2016-06-01 2017-01-01 2018-01-01 12 000 3 2016-06-01 2016-01-01 2017-01-01 12 000 4 2016-01-05 2015-06-01 2016-05-01 11 000 5 2016-03-08 2015-01-01 2016-01-01 68 765

Output:

In that case, when the Date of transaction is before or during the first period, it's just a split of the full period divided by the number of month

 Id Date of Transaction Period Amount 1 2016-01-12 2016-01 1 000 1 2016-01-12 2016-02 1 000 1 2016-01-12 2016-03 1 000 1 2016-01-12 2016-04 1 000 1 2016-01-12 2016-05 1 000 1 2016-01-12 2016-06 1 000 1 2016-01-12 2016-07 1 000 1 2016-01-12 2016-08 1 000 1 2016-01-12 2016-09 1 000 1 2016-01-12 2016-10 1 000 1 2016-01-12 2016-11 1 000 1 2016-01-12 2016-12 1 000

same here:

 Id Date of Transaction Period Amount 2 2016-06-01 2017-01 1 000 2 2016-06-01 2017-02 1 000 2 2016-06-01 2017-03 1 000 2 2016-06-01 2017-04 1 000 2 2016-06-01 2017-05 1 000 2 2016-06-01 2017-06 1 000 2 2016-06-01 2017-07 1 000 2 2016-06-01 2017-08 1 000 2 2016-06-01 2017-09 1 000 2 2016-06-01 2017-10 1 000 2 2016-06-01 2017-11 1 000 2 2016-06-01 2017-12 1 000

A bit more complicated, the date of transaction is after the start of the period but before the end:

 Id Date of Transaction Period Amount 3 2016-06-01 2016-06 6 000 3 2016-06-01 2016-07 1 000 3 2016-06-01 2016-08 1 000 3 2016-06-01 2016-09 1 000 3 2016-06-01 2016-10 1 000 3 2016-06-01 2016-11 1 000 3 2016-06-01 2016-12 1 000

of course the full period can be less than 12 months:

 Id Date of Transaction Period Amount 4 2016-01-05 2016-01 8 000 4 2016-01-05 2016-02 1 000 4 2016-01-05 2016-03 1 000 4 2016-01-05 2016-04 1 000

in that case, easy, the period is before the date of transaction:

 Id Date of Transaction Period Amount 5 2016-03-08 2016-03 68 765

I know that I will multiply the number of lines but that's the requirement

Anyone got an idea.

Thanks a lot

• ###### Re: Split Transactions into Period

May be something like this:

Table:

[Date of Transaction],

[Starting Date],

[Ending Date],

Amount/Count1 as Amount,

Amount as Amount1,

Count;

[Date of Transaction],

[Starting Date],

[Ending Date],

((Year([Ending Date])*12) + Month([Ending Date])) - (((Year([Starting Date])*12)+Month([Starting Date]))) as Count1,

((Year([Date of Transaction])*12) + Month([Date of Transaction])) - (((Year([Starting Date])*12)+Month([Starting Date]))) + 1 as Count,

Amount;

Id, Date of Transaction, Starting Date, Ending Date, Amount

1, 2016-01-12, 2016-01-01, 2017-01-01, 12000

2, 2017-06-01, 2017-01-01, 2018-01-01, 12000

3, 2016-06-01, 2016-01-01, 2017-01-01, 12000

4, 2016-01-05, 2015-06-01, 2016-05-01, 11000

5, 2016-03-08, 2015-01-01, 2016-01-01, 68765

];

NewTable:

[Date of Transaction],

Period,

If(Floor(MonthStart([Date of Transaction])) = Floor(Period), Amount * Count, Amount) as Amount

Where Floor(MonthStart([Date of Transaction])) <= Floor(Period);

[Date of Transaction],

Amount,

Count,

Date(MonthStart([Starting Date], IterNo() - 1), 'YYYY-MM') as Period

Resident Table

While AddMonths([Starting Date], IterNo() - 1) < [Ending Date];

Concatenate(NewTable)

[Date of Transaction],

Date([Date of Transaction], 'YYYY-MM') as Period,

Amount1 as Amount

Resident Table

Where Not Exists(ID, Id);

DROP Tables Table;

• ###### Re: Split Transactions into Period

Maybe like this:

```Data:
*,
if([Date of Transaction]> [Ending Date], Amount,
if(MonthStart([Date of Transaction])<=[Starting Date],Amount/Months,
if([Date of Transaction]>=Date,
Amount*(1-Delta +Delta/Months),
Amount*(Delta)/Months
)
)
) as New_Amount
;
*,
MonthStart([Ending Date],-IterNo()) as Date
WHILE
Iterno() <= Months
;
Id,
[Date of Transaction],
[Starting Date],
[Ending Date],
if([Date of Transaction]> [Ending Date],
1,
if(MonthStart([Date of Transaction])<=[Starting Date],
round(([Ending Date]-[Starting Date])/30) ,
round(([Ending Date]-MonthStart([Date of Transaction]))/30)
)
)  as Months,
if([Date of Transaction]> [Ending Date],
1,
if(MonthStart([Date of Transaction])<=[Starting Date],
1/round(([Ending Date]-[Starting Date])/30),
round(([Ending Date]-MonthStart([Date of Transaction]))/30) / round(([Ending Date]-[Starting Date])/30)
)
)  as Delta,
Amount
INLINE [
Id, Date of Transaction, Starting Date, Ending Date, Amount
1, 2016-01-12, 2016-01-01, 2017-01-01, 12000
2, 2016-06-01, 2017-01-01, 2018-01-01, 12000
3, 2016-06-01, 2016-01-01, 2017-01-01, 12000
4, 2016-01-05, 2015-06-01, 2016-05-01, 11000
5, 2016-03-08, 2015-01-01, 2016-01-01, 68765
];
```
• ###### Re: Split Transactions into Period

Thank you Sunny and Gysbert! Both solution are working perfectly!

I will vote for you Sunny as you were the first one to answer but once again thank you Gysbert...

Now that I've been working with real figures, it turned out that the requirement is a bit different! We need to apply the weight of the days within the months. In the case of Id 1, the result would be:

 Id Date Of Transaction Period Amount Should be 1 2015-01-01 2015-01 1 000,00 1 019,18 1 2015-01-01 2015-02 1 000,00 920,55 1 2015-01-01 2015-03 1 000,00 1 019,18 1 2015-01-01 2015-04 1 000,00 986,30 1 2015-01-01 2015-05 1 000,00 1 019,18 1 2015-01-01 2015-06 1 000,00 986,30 1 2015-01-01 2015-07 1 000,00 1 019,18 1 2015-01-01 2015-08 1 000,00 1 019,18 1 2015-01-01 2015-09 1 000,00 986,30 1 2015-01-01 2015-10 1 000,00 1 019,18 1 2015-01-01 2015-11 1 000,00 986,30 1 2015-01-01 2015-12 1 000,00 1 019,18 Total 12 000,00 12 000,00

Any idea?