Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks, working but i want to dynamically show like this 0-15 one field, 16-30 one field like this
can you please elaborate? what do you mean by dynamically show field? Do you want to create a separate field for each bucket?
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
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
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