Aging bucket in qlikview

[bharani8]

Hi Experts - trying to build an chart with Dimension in days like 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, 181-365, >365..

Am able to do it for frequent interval using class function.. but when it comes to 181-365 and >365  am not able to crack it.. Please help..

[arvind_patil]

Hi Bharani,

Use Dual Function for That:

Dual('0-30',1)

Dual('30-60',2)

.

.

.

Thanks,

Arvind Patil

[bharani8]

Where i need to use this? Plz explain more am just beginner

[MK_QSL]

Use something like below.

Map_Bucket:
Mapping Load 
From + IterNo() - 1 as JoinKey
,Bucket 
While From + IterNo() - 1 <= To;
Load * Inline
[
From, To, Bucket
0, 30, 0-30
31, 60, 31-60
61, 90, 61-90
91, 120, 91-120
121, 150, 121-150
151, 180, 151-180
181, 365, 181-365
];


Load 
AGED_DAYS_RECEIPT_CT
,ApplyMap('Map_Bucket',AGED_DAYS_RECEIPT_CT,'>365') as Bucket
Inline
[
AGED_DAYS_RECEIPT_CT 
4
6
10
20
40
42
55
65
80
91
100
120
130
150
160
180
190
200
220
300
325
350
360
400
];

[arvind_patil]

Hi Bharani,

if I want then I use like this:

if(AGED_DAYS_RECEIPT_CT >=0  and AGED_DAYS_RECEIPT_CT <=180 ,Dual('0-180',1),

if(AGED_DAYS_RECEIPT_CT <=365 ,'180-365',Dual(''180-365'',2),

if(AGED_DAYS_RECEIPT_CT >365 ,Dual(''>365'',3),))) as 'Filed name'

Thanks,

Arvind Patil

[Anil_Babu_Samineni · Accepted solution]

I know this answer is weird, But try

If(AGED_DAYS_RECEIPT_CT > 0 and AGED_DAYS_RECEIPT_CT <= 30, Dual('0-30',1),

If(AGED_DAYS_RECEIPT_CT > 30 and AGED_DAYS_RECEIPT_CT <= 60, Dual('31-60',2),

If(AGED_DAYS_RECEIPT_CT > 60 and AGED_DAYS_RECEIPT_CT <= 90, Dual('61-90',3),

If(AGED_DAYS_RECEIPT_CT > 90 and AGED_DAYS_RECEIPT_CT <= 120, Dual('91-120',4),

If(AGED_DAYS_RECEIPT_CT > 120 and AGED_DAYS_RECEIPT_CT <= 150, Dual('121-150',5),

If(AGED_DAYS_RECEIPT_CT > 150 and AGED_DAYS_RECEIPT_CT <= 180, Dual('151-180',6),

If(AGED_DAYS_RECEIPT_CT > 180 and AGED_DAYS_RECEIPT_CT <= 365, Dual('181-365',7), Dual('>365',8))))))))

[MK_QSL]

Also you can use below for sorting issue.

Map_Bucket:
Mapping Load
From + IterNo() - 1 as JoinKey

,Dual(Bucket,RowNo()) as Bucket

While From + IterNo() - 1 <= To;
Load * Inline
[
From, To, Bucket
0, 30, 0-30
31, 60, 31-60
61, 90, 61-90
91, 120, 91-120
121, 150, 121-150
151, 180, 151-180
181, 365, 181-365
];

[MK_QSL]

Use this

Map_Bucket:

Mapping Load

From + IterNo() - 1 as JoinKey

,Dual(Bucket,RowNo()) as Bucket

While From + IterNo() - 1 <= To;

Load * Inline

[

From, To, Bucket

0, 30, 0-30

31, 60, 31-60

61, 90, 61-90

91, 120, 91-120

121, 150, 121-150

151, 180, 151-180

181, 365, 181-365

];

[bharani8]

Absolutly worked as i was looking for!! TY Samineni!!

Special thanks to Manish and Arvind!!:) Learnt a lot from you both

[MK_QSL]

I know you can't select more than one answer as correct but there are other options also to appreciate someone's help.

Happy Qliking !!

[arvind_patil]

Hi Bharani,

If it is Server your purpose mark it as correct answer.

Thanks,

Arvind Patil