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

[leale1997]

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.

[sunny_talwar]

Try something like this

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

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

[leale1997]

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?

[sunny_talwar · Accepted solution]

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]

[leale1997]

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]

[sunny_talwar]

Try this may be

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