Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Number of days in a month between two dates

Hi All,

I need to calculate total number of days of the month corresponding to each loan:

Suppose ,

Input is 

LoanStart DateEnd Date
107-Jan-1921-Jan-19
224-Dec-1930-Jun-20
304-Nov-1918-Feb-20
428-Dec-1803-Jan-19
511-Feb-2025-Feb-20

 

Output is : 

Dec-19Nov-19
6956

 

 How to calculate the output:

Calculate total number of days for each loan and add all

LoanDec-19Remarks 
10Jan 19 comes before Dec 19 ,so total days is 0 
27Since loan starts on 24Dec ,so we have (31-24)=7 days till end of month
331Since loan starts on 4Nov19 and Ends on 18Feb 20 ,whole Dec 19 is covered,so 31 is total number of days 
431Same as above
50Since loan starts after Dec19, total number of days is 0
 69 

 

Similarly for Nov 19 with the same logic

LoanNov-19
10
20
326
430
50
 56

 

Seeking your help.

Thanks and Regards,

Ruma

Labels (1)
1 Solution

Accepted Solutions
Highlighted

Script

A:
LOAD Loan,
	 Date#("Start Date",'DD-MMM-YY') as [Start Date],
	 Date#("End Date",'DD-MMM-YY') as [End Date];
LOAD * INLINE [
    Loan,	Start Date,	End Date
    1,	07-Jan-19,	21-Jan-19
    2,	24-Dec-19,	30-Jun-20
    3,	04-Nov-19,	18-Feb-20
    4,	28-Dec-18,	03-Jan-20
    5,	11-Feb-20,	25-Feb-20
];

B:
LOAD Loan,
	 "Start Date",
     "End Date",
     Date("Start Date" + IterNo()) as Date,
     Floor(Date("Start Date" + IterNo())) as Num,
     Date(MonthStart(Date("Start Date" + IterNo())), 'MMM-YYYY') as MonthYear
Resident A
While "Start Date" + IterNo() <= [End Date];

DROP Table A;

Note: I changed the End Date for Loan 4 from 03-Jan-19 to 03-Jan-20 because you mentioned that you will see Days for Loan 4 in Jan-19 and Dec-19.

image.png

I did this using While loop, but you can also do this using Interval Match where you Interval Match Start and End Date with your calendar object.

View solution in original post

7 Replies
Highlighted
MVP
MVP

Where are these Dec-19 and Nov-19 coming from? Do you maintain a separate master calendar? Could you share a sample app with all links?

Highlighted
Creator
Creator

Hi Tresesco,

 

Please find the app attached.

 

Thanks and Regards,

Ruma

Highlighted
Creator
Creator

Hi Tresesco,

Any luck?

Regards,

Ruma

 

Highlighted
MVP
MVP

I would recommend this to be handled in the script using date customized association. Is that an option?

Highlighted
Creator
Creator

Yes. Can that be done in the script?

Highlighted
Creator
Creator

Can anyone help please?

 

Regards,

Ruma

Highlighted

Script

A:
LOAD Loan,
	 Date#("Start Date",'DD-MMM-YY') as [Start Date],
	 Date#("End Date",'DD-MMM-YY') as [End Date];
LOAD * INLINE [
    Loan,	Start Date,	End Date
    1,	07-Jan-19,	21-Jan-19
    2,	24-Dec-19,	30-Jun-20
    3,	04-Nov-19,	18-Feb-20
    4,	28-Dec-18,	03-Jan-20
    5,	11-Feb-20,	25-Feb-20
];

B:
LOAD Loan,
	 "Start Date",
     "End Date",
     Date("Start Date" + IterNo()) as Date,
     Floor(Date("Start Date" + IterNo())) as Num,
     Date(MonthStart(Date("Start Date" + IterNo())), 'MMM-YYYY') as MonthYear
Resident A
While "Start Date" + IterNo() <= [End Date];

DROP Table A;

Note: I changed the End Date for Loan 4 from 03-Jan-19 to 03-Jan-20 because you mentioned that you will see Days for Loan 4 in Jan-19 and Dec-19.

image.png

I did this using While loop, but you can also do this using Interval Match where you Interval Match Start and End Date with your calendar object.

View solution in original post