date fields confusion

[murti]

It is one of the most confusing problems while I m dealing with the scripts at first. Currently, I am dealing with the table I uploaded and there is no other tables in my model lately. My problem is, I need to calculate the monthly amounts and there are two types of date fields called payment_date and payment_due_date. Payment_date refers the actual date that the payment has been received. Payment_due_date s are referred to a date that specified earlier between sides. To preserve the simplicity in the dashboard, I can use only one filter pane that will refer to dates. All the tuples contain a payment_due_date value but, if a payment is not successful, payment_date field can be null. Therefore, It will be more accurate to use payment_due_date field while filtering. On the other side, If i m gonna implement it with payment_due_date, I do not know how I am gonna implement the monthly actual payment amounts. I need you to enlight my vision. Help is appreciated!

[TauseefKhan]

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.****

[rwunderlich]

You can create a common date field the represents both payment_date and payment_due_date. See here for a downloadable example for both Qlik Sense and QlikVIew.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob