topic Re: aging of buckets in App Development

aging of buckets

Naresh1 — Thu, 16 Jan 2020 09:36:59 GMT

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

Re: aging of buckets

Vegar — Thu, 16 Jan 2020 10:42:16 GMT

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;

Re: aging of buckets

Naresh1 — Thu, 16 Jan 2020 11:23:18 GMT

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

Re: aging of buckets

Kushal_Chawda — Thu, 16 Jan 2020 12:00:45 GMT

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

Re: aging of buckets

Naresh1 — Thu, 16 Jan 2020 12:03:26 GMT

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

Re: aging of buckets

Vegar — Thu, 16 Jan 2020 13:52:49 GMT

You could use Generic Load.

I've attached an example.

Re: aging of buckets

Kushal_Chawda — Thu, 16 Jan 2020 15:29:42 GMT

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

Re: aging of buckets

Naresh1 — Fri, 17 Jan 2020 10:36:38 GMT

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

Re: aging of buckets

Kushal_Chawda — Fri, 17 Jan 2020 12:31:00 GMT

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