Split rows by date interval

[FedericoC]

Hi,

I have some trouble in develop a script for thi kind of problem: table of general ledger. 

In the row I have three dates: Registration Date, Date Start Competence, Date End Competence, Amount.

For financial issues I use Registration Date but for management account I have to split the amout for every month in the interval between Date Start Competence, Date End Competence.

I can define to use only the last day of the month also if Date is different. This is an example of data:

load * inline [
conto, descrizione, dr, dic, dfc, importo
100, acquisti, 01/01/2023,01/11/2022,31/12/2022,12000
200, servizi,01/08/2022,01/07/2022,31/12/2022,6000
300, assicurazioni,01/12/2022,01/01/2022,31/12/2022,120000
];

This is the initial situation

Account	Desc	Date Reg	Date Start	Date End	Amount
100	acquisti	01/01/2023	01/11/2022	31/12/2022	12.000
200	servizi	01/08/2022	01/07/2022	31/12/2022	6.000
300	assicurazioni	01/12/2022	01/01/2022	30/06/2022	12.000

And this is what I need:

Account	Desc	Date Reg	Date Start	Date End	Amount	Date Manag Acc
100	goods	01/01/2023	01/11/2022	31/12/2022	6.000	30/11/2022
100	goods	01/01/2024	01/11/2022	31/12/2022	6.000	31/12/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	31/07/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	31/08/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	30/09/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	31/10/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	30/11/2022
200	services	01/08/2022	01/07/2022	31/12/2022	1.000	31/12/2022
300	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	31/01/2022
301	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	28/02/2022
302	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	31/03/2022
303	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	30/04/2022
304	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	31/05/2022
305	assurance	01/12/2022	01/01/2022	30/06/2022	2.000	30/06/2022

The total is always 30,000. I think also to use days instead months calculating a daily rateo but It's probably more complex.

I think to use subfield (ex 31/01/2022,28/02/2022,31/03/2022,....) but I don't able to set a string whit all dates, 

Someone can help me?

Thanks!

[rwunderlich · Accepted solution]

Something like this perhaps:

Data:
LOAD * Inline [
Account, Desc, Date Reg, Date Start, Date End, Amount
100, acquisti, 01/01/2023, 01/11/2022, 31/12/2022, 12000
200, servizi, 01/08/2022, 01/07/2022, 31/12/2022, 6000
300, assicurazioni, 01/12/2022, 01/01/2022, 30/06/2022, 12000
]
;

Distributed:
LOAD *
  ,Date(Floor(MonthEnd(AddMonths([Date Start], IterNo()-1, 1)))) as [Date Manage Acc]
Resident Data
While AddMonths([Date Start], IterNo()-1, 1) <= [Date End]
;
Drop Table Data;

Join (Distributed)
LOAD
  Account,
  Amount / Count as [Distributed Amount]
;
LOAD
  Account,
  Amount,
  count(Account) as Count
Resident Distributed
Group by Account, Amount
; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[FedericoC]

For me it would have been really important to have an answer, but to have the right one directly from the great Rob was incredible!!
I met you at Qonnection some years Ago!!
Thank you very much Rob!