Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bharani8
Creator III
Creator III

Aging bucket in qlikview

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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

21 Replies
jmvilaplanap
Specialist
Specialist

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

bharani8
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

Hi Bharani,

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

Thanks,

Arvind Patil

bharani8
Creator III
Creator III
Author

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

arvind_patil
Partner - Specialist III
Partner - Specialist III

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

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

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
Creator III
Creator III
Author

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

Caaapture.PNG

bharani8
Creator III
Creator III
Author

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