topic Re: Aging bucket in qlikview in QlikView

Aging bucket in qlikview

bharani8 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aging bucket in qlikview

jmvilaplanap — Mon, 03 Jul 2017 11:56:57 GMT

Maybe there is no data for these values, isn't it?

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 12:00:33 GMT

Data is there.. but still i get it as 30 days interval for all.. i tried with..

=if(AGED_DAYS_RECEIPT_CT >=0 ,$(vAgingGrp),

if(AGED_DAYS_RECEIPT_CT <=180 ,$(vAgingGrp),

if(AGED_DAYS_RECEIPT_CT >=181 ,replace(class(AGED_DAYS_RECEIPT_CT,185),'<= x <','-'),

if(AGED_DAYS_RECEIPT_CT >=366 ,replace(class(AGED_DAYS_RECEIPT_CT,10000),'<= x <','-')))))

vAgingGrp = Replace(class(AGED_DAYS_RECEIPT_CT,30),'<= x <','-')

Re: Aging bucket in qlikview

arvind_patil — Mon, 03 Jul 2017 12:02:24 GMT

Hi Bharani,

please Share what you have done . I will try from my end.

Thanks,

Arvind Patil

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 12:03:56 GMT

Hi Arvind - Plz chk above .. i have replied to Pascual

Re: Aging bucket in qlikview

arvind_patil — Mon, 03 Jul 2017 12:12:15 GMT

Hi Bharani,

May be you are trying like this:

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

if(AGED_DAYS_RECEIPT_CT <=365 ,'180-365',

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

Thanks,

Arvind Patil

Re: Aging bucket in qlikview

MK_QSL — Mon, 03 Jul 2017 12:20:47 GMT

Try something like this

I have considered Age field. You can change according to your need.

IF(Age >= 0 and Age < 180, Class(Age,30),

IF(Age <= 365, '180-365','>365'))

Re: Aging bucket in qlikview

jmvilaplanap — Mon, 03 Jul 2017 12:28:48 GMT

Why don't use the Class function only? Why do you do all those "if"s?

I think is more easy than you are doing.

You have yo use class(field) as a dimension and count or sum as expression.

It makes sense for you?

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 12:36:21 GMT

i get the required output... but 2 challenges i see..

one is - it says..0-30 30-60 60-90 but i need 0-30..31-60..61-90

and second is Sorting..

i used below one in the script..

if(AGED_DAYS_RECEIPT_CT >=0 and AGED_DAYS_RECEIPT_CT <=180 , Replace(class(AGED_DAYS_RECEIPT_CT,30),'<= x <','-'),

if(AGED_DAYS_RECEIPT_CT <=365 ,'181-365',

if(AGED_DAYS_RECEIPT_CT >365 ,'>365'))) as 'Aging_Bucket'

got below output

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 12:40:31 GMT

i get the required output... but 2 challenges i see..

one is - it says..0-30 30-60 60-90 but i need 0-30..31-60..61-90

and second is Sorting..

Re: Aging bucket in qlikview

arvind_patil — Mon, 03 Jul 2017 12:47:20 GMT

Hi Bharani,

Use Dual Function for That:

Dual('0-30',1)

Dual('30-60',2)

Thanks,

Arvind Patil

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 12:49:53 GMT

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

Re: Aging bucket in qlikview

MK_QSL — Mon, 03 Jul 2017 12:53:24 GMT

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
];

Re: Aging bucket in qlikview

arvind_patil — Mon, 03 Jul 2017 12:55:07 GMT

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

Re: Aging bucket in qlikview

Anil_Babu_Samineni — Mon, 03 Jul 2017 12:55:15 GMT

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))))))))

Re: Aging bucket in qlikview

MK_QSL — Mon, 03 Jul 2017 12:56:45 GMT

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
];

Re: Aging bucket in qlikview

MK_QSL — Mon, 03 Jul 2017 12:57:11 GMT

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

];

Re: Aging bucket in qlikview

bharani8 — Mon, 03 Jul 2017 13:01:47 GMT

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

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

Re: Aging bucket in qlikview

MK_QSL — Mon, 03 Jul 2017 13:04:40 GMT

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 !!

Re: Aging bucket in qlikview

arvind_patil — Mon, 03 Jul 2017 13:06:00 GMT

Hi Bharani,

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

Thanks,

Arvind Patil