Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
srinivasa1
Creator II
Creator II

ageing analysis

I would  like to do
ageing analysis  for time and
duration for message  like [0-2][2-10][5-20][>20 hrs]

Time duration  will be like below

10:06:32 ,

82:05:30 etc

Pls share with me if you  ageing analysis like this. Thanks in advance.

12 Replies
whiteline
Master II
Master II

Hi.

[0-2][2-10][5-20][>20 hrs]

Do you really use overlapping ranges, or it's a mistake ?

srinivasa1
Creator II
Creator II
Author

Hi Sorry its typo.

it will be like

[0-2] [3-10][11-20] [>20 hrs]

Thanks

MayilVahanan

Hi

Try like this

if(hour(timefield) <= 2, '[0-2]',if(hour(timefield) <= 10, '[3-10]', if(hour(timefield)) < = 20,'[11-20]','[>20 hrs]'))) as aging

or use applymap function

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
almamy_diaby
Creator
Creator

I think this formula is more appropriate:

if(hour(timefield) <= 2, '[0-2]',if(hour(timefield) <= 10 and  hour(timefield) > 2 , '[3-10]', if(hour(timefield)) < = 20 and hour(timefield) > 10,'[11-20]','[>20 hrs]'))) as aging

whiteline
Master II
Master II

You can't use hour() function if you want to classify periods greater than 24 hours.

div(time*24*60,60) is a good alternative.

In this particular case the calssification can be done without if()s:

=pick(rangemin(div(div(time*24*60,60),3)+1, div(div(time*24*60,60)-1,10)+2, 4), '[0-3)', '[3-11)', '[11-20)', '[>20 hrs]')

I've changed the intervals a little as there are minutes after an hour and I guess you want 2:10 to be classified as [0-2].

Also  nulls() are not handled and classified as '[>20 hrs]'.

srinivasa1
Creator II
Creator II
Author

HI Thanks for your reply.  im trying write code as below but my and condtion  not working can you pls let me know why ist not working

Load




   
if([ Time] <= 1  , '[0-1]') ,

    
if([Time] <= 3  AND if([Time] >=1 ,'[2-3]'),

   
if([Time] <= 5 , '[4-5]',

   
if([Time] >= 5, '[5-20]'))) as aging


    FROM TAB1;

whiteline
Master II
Master II

What are the values of your [Time] field ?

almamy_diaby
Creator
Creator

you have a if syntax problem. truy this :

Load


   
if([ Time] <= 1  , '[0-1]',

    
if([Time] <= 3  AND if([Time] > 1 ,'[2-3]',

   
if([Time] <= 5  and [Time] > 4, '[4-5]',


   
if([Time] > 5, '[5-20]'))) ) as aging

FROM TAB1;

srinivasa1
Creator II
Creator II
Author

Hi,

Thanks for reply .i have tried its shows as below error

Error in expression:
If takes 2-3 parameters

Thanks