Question on Date Formatting and Interval Function

[Anonymous]

   Hello,

I'm working on a caseworker dashboard and I'm having some trouble with my "CaseAge" time stamp field. From the data file I receive the total time worked on a case in the following format:

508:18:53

That would be 508 Hours, 18 Minutes, and 53 Seconds.

I'm working on creating some categories in my script so that I can quickly filter on hours. I've created the following table:

Interval:

Load

interval(interval#(CaseAge,'hh:mm:ss'),'hh:mm:ss') as CaseAge,

IF([CaseAge] >='00:00:00' and [CaseAge]<= '04:00:00', 'CategoryA',

IF([CaseAge] >='04:00:01' and [CaseAge]<='08:00:00', 'CategoryB',

IF([CaseAge] >='8:00:01' and [CaseAge]<='12:00:00', 'CategoryC',

IF([CaseAge] >='12:00:01' and [CaseAge]<='24:00:00', 'CategoryD',

IF([CaseAge] >='24:00:01' and [CaseAge]<='48:00:00', 'CategoryE',

IF([CaseAge] >='48:00:01' , 'CategoryF', )))))) as IntervCat

Resident Facts;

Any idea why this is not working? I should be left with six categories that bucket the Case Age based on the intervals defined about 0-4 hrs', 4-8 hrs, etc.

Any help would be much appreciated.

Best Regards,

Darren

[sunny_talwar · Accepted solution]

How about this?

IF([CaseAge] >= 0 and [CaseAge]<= 4/24, 'CategoryA',

IF([CaseAge] > 4/24 and [CaseAge]<=8/24, 'CategoryB',

IF([CaseAge] > 8/24 and [CaseAge]<= 12/24, 'CategoryC',

IF([CaseAge] > 12/24 and [CaseAge]<=24/24, 'CategoryD',

IF([CaseAge] > 24/24 and [CaseAge]<= 48/24, 'CategoryE',

IF([CaseAge] > 48/24 , 'CategoryF', )))))) as IntervCat

Resident Facts;

[Anonymous]

Thanks Sunny,

I think that worked. I've added 'Date' from my Fact Table and a counter and it seems to have done the trick. THank you for your help.