Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing dates on two fields

Hello!

I am working on a study project to build a management dashboard for a school project.
I am far ahead of my classmates, because i find this very interesting to work on.

I am having some issues I can't solve myself. I have searched for common questions and i see them asked frequently but non of the solutions seem to solve my 'problem'.

I have a very small dataset to work with.


I have a table that looks like this:


ID               |     Field1 (added)     |         Field2 (closed)

1                            28-3-2014               1-4-2014   

2                            29-3-2014               4-4-2014   

3                            30-3-2014               3-4-2014

Note: the dates are in European style. And the data represents customer cases.

What i am trying to do is the following:

I want to show how many of the ID's is overdue in percent.
For example, A 'case' may not be open for more than 3 days, if so, its overdue.


I find it very hard to compare dates so I can say there are 3 days between them.
Does anyone have some suggestions?

Best regards

Joerie

3 Replies
anbu1984
Master III
Master III

Load ID From Table

where Date#(Field2,'D-M-YYYY') - Date#(Field1,'D-M-YYYY') > 3

magavi_framsteg
Partner - Creator III
Partner - Creator III

Hi.

If you work with dates it is very easy.

Each date corresponds to an integer, ie. 43585.

So..

If you did for example:

if (date(Field2) - date(Field1) > 3, 1, 0) as _IsOverdue

If you have problems with the date format, you can do it the bad way:

if (

makedate(subfield(Field2, '-', 3), subfield(Field2, '-', 2), subfield(Field2, '-', 1)

-

makedate(subfield(Field1, '-', 3), subfield(Field1, '-', 2), subfield(Field1, '-', 1)

> 3, 1, 0

AS _IsOverdue

Hope it helps.

Roop
Specialist
Specialist

I would tend to perform the analysis as above but with one major change. I would create a DateVar field which would show you the difference in days between the fields in the same way as demonstrated by Anbu Cheliyan. This would allow you to dynamically select what you wanted (such as more than 3 days).

This approach is of particular use if you are not sure what your important metrics are and their individual relevance.

So create the field:

Date#(Field2,'D-M-YYY') - Date#(Field1, 'D-M-YYY') as DateVar in your load statement.