topic ageing analysis in QlikView

ageing analysis

srinivasa1 — Mon, 05 Nov 2012 16:27:31 GMT

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.

Re: ageing analysis

whiteline — Mon, 05 Nov 2012 16:38:27 GMT

Hi.

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

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

Re: ageing analysis

srinivasa1 — Mon, 05 Nov 2012 17:02:34 GMT

Hi Sorry its typo.

it will be like

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

Thanks

Re: ageing analysis

MayilVahanan — Mon, 05 Nov 2012 17:08:01 GMT

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

Re: ageing analysis

almamy_diaby — Mon, 05 Nov 2012 17:34:06 GMT

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

Re: ageing analysis

whiteline — Mon, 05 Nov 2012 18:49:30 GMT

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]'.

Re: ageing analysis

srinivasa1 — Tue, 06 Nov 2012 01:09:00 GMT

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;

Re: ageing analysis

whiteline — Tue, 06 Nov 2012 05:46:06 GMT

What are the values of your [Time] field ?

Re: ageing analysis

almamy_diaby — Tue, 06 Nov 2012 09:04:38 GMT

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;

Re: ageing analysis

srinivasa1 — Tue, 06 Nov 2012 14:36:18 GMT

Hi,

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

Error in expression:
If takes 2-3 parameters

Thanks

Re: ageing analysis

MayilVahanan — Tue, 06 Nov 2012 14:46:18 GMT

Try like this

Load

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

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

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

FROM TAB1;

Load *,

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

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

    if([Time] <= 5 '[4-5]', '[6-20]'))) as aging

FROM TAB1;

Both are same..

Re: ageing analysis

whiteline — Tue, 06 Nov 2012 15:27:36 GMT

Remove the space in first If():

[ Time]

Re: ageing analysis

almamy_diaby — Tue, 06 Nov 2012 16:02:33 GMT

this expression seems to me proper:

Load

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

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

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

FROM TAB1;