Monthly Trend Analysis

[krmvacar]

Hi All, 

Here, according to my date field, if there is any money due, I want to write it down and proportion it to the total amount. What I need to pay attention to here is to look at the due date in the previous months every month and check whether the payment has been made at the same time. Your help is appreciated.I can do this in script and frontend, no problem.

Invoice date	Due Date	Payment date	Document no	Amount	Date
15.01.2023	14.02.2023	15.04.2023	15	1.500	31.01.2023
14.02.2023	16.03.2023	15.05.2023	16	1.500	28.02.2023
16.03.2023	15.04.2023	14.06.2023	17	1.500	31.03.2023
15.04.2023	15.05.2023	14.07.2023	18	1.500	30.04.2023
15.05.2023	14.06.2023	13.08.2023	19	1.500	31.05.2023

I want to result:

Invoice date	Due Date	Payment date	Document no	Amount	Date	Overdue	Sales Amount	Ratio
15.01.2023	14.02.2023	15.04.2023	15	1.500	31.01.2023	-	1500	0%
14.02.2023	16.03.2023	15.05.2023	16	1.500	28.02.2023	1.500	3000	50%
16.03.2023	15.04.2023	14.06.2023	17	1.500	31.03.2023	3.000	4500	67%
15.04.2023	15.05.2023	14.07.2023	18	1.500	30.04.2023	3.000	6000	50%
15.05.2023	14.06.2023	13.08.2023	19	1.500	31.05.2023	3.000	7500	40%

[Ahidhar]

could you explain how you want the Overdue column based on previous Amount ,

how do you want to compare due date and payment date

[krmvacar]

Hi ,

For the overdue field, we need to calculate it by taking into account 2 fields: Due Date and Payment Date fields. If the duedate field is smaller than the date field and larger than the payment date field, we can calculate it. If the payment date field is larger, then we write a negative amount.

For example for  date=30.04.2023

we have There are 3 small calculations Due date and one payment date occured.

4500-1500=3000

[Ahidhar · Accepted solution]

there maybe a better way though this should do

tab:
load
date#("Invoice date",'DD.MM.YYYY') as "Invoice date",
date#("Due Date",'DD.MM.YYYY') as "Due Date",
date#("Payment date",'DD.MM.YYYY') as "Payment date",
"Document no",
Amount,
date#(Date,'DD.MM.YYYY') as Date;
load *
Inline
[
Invoice date, Due Date, Payment date,Document no, Amount, Date
15.01.2023, 14.02.2023, 15.04.2023, 15, 1.500, 31.01.2023
14.02.2023, 16.03.2023, 15.05.2023, 16, 1.500, 28.02.2023
16.03.2023, 15.04.2023, 14.06.2023, 17, 1.500, 31.03.2023
15.04.2023, 15.05.2023, 14.07.2023 ,18,1.500, 30.04.2023
15.05.2023, 14.06.2023, 13.08.2023 ,19,1.500, 31.05.2023
];

for i = 0 to 4
t1:
load
"Invoice date" as id,
"Due Date" as dd,
"Payment date" as pd,"Document no" as dn,Amount as a,Date as d,
if(Date=peek('Date',$(i),'tab'),1,0) as Flag,
if(peek('Date',$(i),'tab')>"Due Date",1,0) as Flag1,
if(peek('Date',$(i),'tab')>"Payment date",1,0) as Flag2
resident tab;

t2:
load *,
if(Flag1=1,rangesum(peek(a1),a),0) as a1,
if(Flag2=1,rangesum(peek(a2),a),0) as a2
Resident t1;drop table t1;

t3:
load
max(a1) as maxa1,max(a2) as maxa2
Resident t2;

Concatenate(tab)
t4:
load
id as "Invoice date",
dd as "Due Date",pd as "Payment date",dn as "Document no",a as Amount,d as Date,
peek('maxa1',0,'t3')-peek('maxa2',0,'t3') as Overdue
resident t2 where Flag=1;drop table t3;drop table t2;
next i;

NoConcatenate
tab1:
load *
resident tab where Overdue<>null();
drop table tab;