topic Qlik Age analysis? in QlikView

Qlik Age analysis?

Fri, 19 Dec 2014 10:51:57 GMT

Dear All,

My sample pivot table is like below,

Date	debit note	amount	0-30	31-60	61-90	over 90
1/01/2014	BAD0001	1000
5/02/2014	BAD0002	1500
8/03/2014	BAD0003	2500
10/10/2014	BAD0004	1250
12/11/2014	BAD0005	2000

How I could write a formula to categorized the amount to the correct age group? I'm looking forward to have a sample formula(expression) for this.

Regards,

Priyantha.

Re: Qlik Age analysis?

Anonymous — Fri, 19 Dec 2014 11:04:51 GMT

Hi,,

Create buckets using interval function:

If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date(Date/Time,'DD-MM-YYYY hh:mm:ss'),'dd'))<=30, '0-30',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date(Date/Time,'DD-MM-YYYY hh:mm:ss'),'dd'))<=60, '31-60',
If((interval(Date(Today(),'DD-MM-YYYY hh:mm:ss')-Date(Date/Time,'DD-MM-YYYY hh:mm:ss'),'dd'))<=90, '61-90','>90'))) as ByAge,

regards

Neetha

Re: Qlik Age analysis?

alkesh_sharma — Fri, 19 Dec 2014 11:20:13 GMT

Its better to create age buckets @ script level rather than UI

Script would be

LOAD ....

...

IF((Today() - <YourDate>)<0, Dual('Not Due',0),

IF((Today() - <YourDate>)<=30, Dual('0-30',1),

IF((Today() - <YourDate>)<=60, Dual('30-60',2),

IF((Today() - <YourDate>)<=90, Dual('60-90',3), Dual('Over 90',4) AS AGE_Bucket

FROM

Re: Qlik Age analysis?

Anonymous — Fri, 19 Dec 2014 11:22:07 GMT

the above code is for script level

Re: Qlik Age analysis?

alkesh_sharma — Fri, 19 Dec 2014 11:24:41 GMT

Yes,

You can use the same script @ UI as well but it is generally not recommended as this would make your application slower as the data increases.

Re: Qlik Age analysis?

Anonymous — Fri, 19 Dec 2014 11:30:40 GMT

Yes,it will make slow performance wise and maintenance wise.

if buckets created at script level,we can use them in all objects

depending on requirements.

Thanks

Neetha

Re: Qlik Age analysis?

alkesh_sharma — Fri, 19 Dec 2014 11:35:06 GMT

Please mark it correct if it solved ur purpose.

Thanx