# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Honored Contributor

## Display tickets by age

Hi,

Please can any one help me with below query i need to calculate age of open tickets in script.

Below is my script:

If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

please can you tell me what am i doing wrong.

Regards

Nitha

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Employee

## Re: Display tickets by age

Then it could be that [Call Closed - Date/Time] never is NULL. Empty field is not the same as NULL.

Try

Len(Trim([Call Closed - Date/Time]))=0

HIC

30 Replies
Highlighted
Honored Contributor II

## Re: Display tickets by age

I think the problem is that you're using the date formatter versus the date transformer. If your [Call Opened - Date/Time] field is in format DD-MM-YYYY, then this should return the interval between today and that date, in days:

today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))

There's actually no need to even use interval if you want the difference in days.

Regards,

Highlighted
Employee

## Re: Display tickets by age

You have three If() functions that all demand IsNull([Call Closed - Date/Time]). Is this really what you want? This means that you only assign values if this field is NULL. It also means that the 2nd and 3rd If() are meaningless, since they always will evaluate to FALSE.

HIC

Highlighted
Partner

## Re: Display tickets by age

I think the problem is in the use of the IsNull function. If the field is null (as you are testing) you cannot substract it from today(). Maybe you must change to

If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) <> -1 and (interval(Date([Call Closed - Date/Time],'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

Eduardo

Highlighted
Honored Contributor

## Re: Display tickets by age

Hi Henric,

sorry for mistake,below is what my script looks like:

If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=1, 'P1',
If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=5, 'P2',
If(IsNull([Call Closed - Date/Time]) and (interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD'))<=20, 'P3')))) as OpenByAge,

I want to calculate age of open tickets till today date when closed date column doesn't have any value.

Thanks

Nitha

Highlighted
Employee

## Re: Display tickets by age

Then it could be that [Call Closed - Date/Time] never is NULL. Empty field is not the same as NULL.

Try

Len(Trim([Call Closed - Date/Time]))=0

HIC

Highlighted
Honored Contributor

## Re: Display tickets by age

Hi Henric,

i am using below script as per your suggestion:

If

(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=1, 'P1',
If(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=5, 'P2',
If(Len(Trim([Call Closed - Date/Time])) = 0 and interval(Date(Today(),'DD-MM-YYYY')-Date([Call Opened - Date/Time],'DD-MM-YYYY'),'DD')<=20, 'P3'))) as OpenByAge,

once script is loaded ,no value is shown in OpenByage listbox .

Regards

Nitha

Highlighted
Honored Contributor II

## Re: Display tickets by age

Nitha, give this a try:

if(len(trim([Call Closed - Date/Time]))>0,

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=1,

'P1',

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=5,

'P2',

if(today() - floor(date#([Call Opened - Date/Time],'DD-MM-YYYY'))<=5,

'P3'

)

)

)

) as OpenByAge

Regards,

Highlighted
Honored Contributor

## Re: Display tickets by age

Thanks ,but still its not populating data in OpenByAge list box

Regards

Nitha

Highlighted
Honored Contributor II

## Re: Display tickets by age

What's the format of [Call Opened - Date/Time]? Can you post one complete value of this field?

Thanks,