topic Re: Comparing dates on two fields in QlikView

Comparing dates on two fields

Mon, 12 May 2014 14:11:48 GMT

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

Re: Comparing dates on two fields

anbu1984 — Mon, 12 May 2014 14:18:00 GMT

Load ID From Table

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

Re: Comparing dates on two fields

magavi_framsteg — Mon, 12 May 2014 14:22:27 GMT

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.

Re: Comparing dates on two fields

Roop — Mon, 12 May 2014 14:26:23 GMT

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.