Hi @murti,
To deal with payment_date and payment_due_date in a way that maintains simplicity in your dashboard, we need to create a unified approach that combines both dates effectively for filtering and calculation purposes.
Create a unified date field that you can use for filtering, which primarily uses payment_due_date but also considers payment_date with the help of Master Calendar.
LET vEndDate = num(today(1)); // End at today's date
LET vStartDate = num(MakeDate(2012,1,1)) - 1;
LET vMinDate = Num(Min(vStartDate));
LET vMaxDate = Num(Max(vEndDate));
AllPayments: //YourTable
MasterCalendar:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;
DataWithUnifiedDate:
LOAD
*,
Date(
If(IsNull(payment_date), payment_due_date, payment_date)
) AS UnifiedDate
RESIDENT AllPayments;
Left Join (DataWithUnifiedDate)
LOAD
Date as UnifiedDate,
Year(Date) as Year,
Month(Date) as Month
RESIDENT MasterCalendar;
To display the total amounts considering the actual payment dates:
MonthName(payment_due_date) as MonthYear,
Sum({<payment_date={"=payment_date"}>} Amount) as MonthlyPaymentAmount
***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****
