aging of buckets

[Naresh1]

Hi all,

iam new to Qliksense, i want  to aging of buckets range is 0-15, 16-30, 31-60,61-120,121-180, >180 like this using over_due_days column, can anyone tell me , all these are 0-15, 16-30, 31-60,61-120,121-180, >180 come like fields 

[Vegar]

I guess you could use intervalmatch. Take a look at this example and adjust it to your needs. 

for i = 230 to 1 step -40
TransactionData:
LOAD 
	floor(rand()*$(i)) as overdue_days
AutoGenerate 230-$(i)
;
next 
;

Intervals:
IntervalMatch(overdue_days) 
LOAD * inline [
Start, End 
0,15 
16,30 
31,60
61,120
121,180
181,
];
Left join
LOAD 
	overdue_days,
	dual(Start & '-' & End, Start) as AgingBucket
Resident Intervals;

[Naresh1]

Thanks, working but i want to dynamically show like this 0-15 one field, 16-30 one field like this

[Kushal_Chawda]

can you please elaborate? what do you mean by dynamically show field? Do you want to create a separate field for each bucket?

[Naresh1]

yes, i want to create separate field for each bucket range is 0-15, 16- 30, 31-60, 61-120, 121-180, >180 like this

[Vegar]

You could use Generic Load.

I've attached an example. 

[Kushal_Chawda]

another approach

let vBucketField = 'overdue_days'; // Define field name in your data on which you want to generate bucket. Make sure field name should be same

Intervals:
Load Concat(chr(10)&BucketCondition,',') as BucketFinal;
Load *,
     'if('&Start&if(Bucket like '*-*',' and ')&End&','&'$(vBucketField)) as ['&Bucket&']' as BucketCondition;
Load *,
     if(Bucket like '*-*','$(vBucketField)>='&SubField(Bucket,'-',1),'$(vBucketField)'&Bucket) as Start,
     if(Bucket like '*-*','$(vBucketField)<='&SubField(Bucket,'-',2),'') as End;
LOAD * inline [
Number,Bucket
1,0-15 
2,16-30 
3,31-60
4,61-120
5,121-180
6,>180 ];

Let vBucketCondition =Peek('BucketFinal',0,'Intervals');

Now you can use this vBucketCondition variable into your actual load statement  same like a field

Data:
LOAD *,
     $(vBucketCondition)
FROM YourQVD

[Naresh1]

i want this type of bucketing, i want fields like this using over_due_days field

[Kushal_Chawda]

You can dynamically create the Buckets in you data with below approach. It will be easy for you to just update the Buckets in inline table.

let vBucketField = 'over_due_days'; // Define actual field name in your data on which you want to generate Bucket.eg. over_due_days

let vBucketColumnName = '[Over Due Bucket]'; // Provide the name you want to give for generated Bucket field.

Intervals:
Load Concat(chr(10)&BucketCondition,',')& Repeat(')',Count(Number))& ' as '&'$(vBucketColumnName)' as BucketFinal;
Load *,
     'if('&Start&if(Bucket like '*-*',' and ')&End&','&'dual('&chr(39)&Bucket&chr(39)&','&Number&')' as BucketCondition;
Load *,
     if(Bucket like '*-*','$(vBucketField)>='&SubField(Bucket,'-',1),'$(vBucketField)'&Bucket) as Start,
     if(Bucket like '*-*','$(vBucketField)<='&SubField(Bucket,'-',2),'') as End;
LOAD * inline [
Number,Bucket
1,0-15 
2,16-30 
3,31-60
4,61-120
5,121-180
6,>180 ];

Let vBucketCondition =Peek('BucketFinal',0,'Intervals');

Now call the Variable in your actual data load which will create a Bucket field for you

Data: 
Load *,
     $(vBucketCondition)
FROM YourData

Next step, Create a pivot table with Dimension Over_Due_Bucket and Expression Sum(Over_due_amount)

In pivot table, move Over_Due_Bucket in Column so that You will get horizontal view of Bucket values