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
Did you mean:
Highlighted
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

 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-19 5 11-Feb-20 25-Feb-20

Output is :

 Dec-19 Nov-19 69 56

How to calculate the output:

Calculate total number of days for each loan and add all

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

Similarly for Nov 19 with the same logic

 Loan Nov-19 1 0 2 0 3 26 4 30 5 0 56

Thanks and Regards,

Ruma

Labels (1)
• Qlik Sense

1 Solution

Accepted Solutions
Highlighted
MVP

Script

``````A:
Date#("Start Date",'DD-MMM-YY') as [Start Date],
Date#("End Date",'DD-MMM-YY') as [End Date];
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:
"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.

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.

7 Replies
Highlighted
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

Hi Tresesco,

Thanks and Regards,

Ruma

Highlighted
Creator

Hi Tresesco,

Any luck?

Regards,

Ruma

Highlighted
MVP

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

Highlighted
Creator

Yes. Can that be done in the script?

Highlighted
Creator

Regards,

Ruma

Highlighted
MVP

Script

``````A:
Date#("Start Date",'DD-MMM-YY') as [Start Date],
Date#("End Date",'DD-MMM-YY') as [End Date];
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: