Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to calculate the number of days past due date. Where date has formatting issues.

I need to calculate the days past due for open records. 

This should be simple.  Is there a Closed_Date, if yes, what are the number of days between today and the due date.

I tried a very basic:

[Date_Due] - Today() as [Past Due Days]

This gives me a field with no values.  I think the format of the [Date_Due] field is causing the issue.

I tried: 

Date(Date#([Date_Due] - Today())) as [Past Due Days],

Still got a field with no values.

What I really want is something like this: 

if(isnull([Date_Closed]),([Date_Due] - Today()),'On Target') as [Past Due Days]

Ultimately I would like to group the outcome of the equations in 30, 60, 90 and 90+ days past due. I was thinking that might be easier if I created a variable but I am not sure.

Labels (2)
1 Solution

Accepted Solutions
Highlighted

I am not sure what you mean when you say universal fix? If all your dates are in particular format, then make sure that the environmental variable is updated to show the particular format... as that dictate what QlikView/Qlik Sense understands as a date. If you have multiple sources with multiple different formats, then you can use Date#() to help Qlik understand your date as date field.

In your particular example, if you are not sure what is Qlik reading Date_Due as the format, I would just load Date_Due as a field and see what format it shows up as... if it shows up as MM/DD/YY... then I would try this

Date#([Date_Due], 'MM/DD/YY') - Today() as [Past Due Days]

View solution in original post

5 Replies
Highlighted

Try something like this

Date#([Date_Due], 'FormatDate_DueAppearsToBeIn') - Today() as [Past Due Days]

Replace FormatDate_DueAppearsToBeIn with the Date format for Date_Due....

Highlighted
Contributor III
Contributor III

I am looking at the data in a microsoft access table connected to SQL.  The date format I see in Access and in QlikView is mm/dd/yy.   But I am afraid there is a different date format in the SQL database that I am unable to see.  Is there anything that is a universal fix for date formats?

Highlighted

I am not sure what you mean when you say universal fix? If all your dates are in particular format, then make sure that the environmental variable is updated to show the particular format... as that dictate what QlikView/Qlik Sense understands as a date. If you have multiple sources with multiple different formats, then you can use Date#() to help Qlik understand your date as date field.

In your particular example, if you are not sure what is Qlik reading Date_Due as the format, I would just load Date_Due as a field and see what format it shows up as... if it shows up as MM/DD/YY... then I would try this

Date#([Date_Due], 'MM/DD/YY') - Today() as [Past Due Days]

View solution in original post

Highlighted
Contributor III
Contributor III

That formula worked.  Can you help me weed out the closed records with this If statement?

 

If(IsNull([Date_Closed_Done],Date#([Date_Due], 'MM/DD/YY') - Today(), 'Closed')) as [Past Due Days]

Highlighted

Try this may be

If(IsNull([Date_Closed_Done]),
Date#([Date_Due], 'MM/DD/YY') - Today(),
'Closed')
as [Past Due Days]