Qlik Age analysis?

[#1]

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.

[Anonymous]

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

[alkesh_sharma]

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

<Table Name>

[Anonymous]

the above code is for script level

[alkesh_sharma]

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.

[Anonymous]

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

[alkesh_sharma]

Please mark it correct if it solved ur purpose.

Thanx