Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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!