Solved: Aging bucket in qlikview - Qlik Community

bharani8 · ‎2017-07-03

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

Anil_Babu_Samineni · ‎2017-07-03

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

jmvilaplanap · ‎2017-07-03

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

bharani8 · ‎2017-07-03

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

arvind_patil · ‎2017-07-03

Hi Bharani,

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

Thanks,

Arvind Patil

bharani8 · ‎2017-07-03

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

arvind_patil · ‎2017-07-03

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

MK_QSL · ‎2017-07-03

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

jmvilaplanap · ‎2017-07-03

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?

bharani8 · ‎2017-07-03

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

bharani8 · ‎2017-07-03

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