Want Dynamic Ageing Bucket

Tue, 04 Nov 2014 05:32:33 GMT

Hi All,

I have to calculate outstanding ageing bucket week wise for corresponding DistParentId.
The bucket calculations are in the attached sheet.

I have implemented it in the script using following code:

Ageing_Bucket:

LOAD

WeekName,

DistParentId,
if(Peek(DistParentId)= DistParentId and Previous(WeekName)< WeekName ,peek(OS_Total)-CollectionAmt,0) as '>7Days',
if(Peek(DistParentId,-2)= DistParentId and Peek(WeekName,-2)< WeekName,peek(OS_Total,-2)- RangeSum(Peek(CollectionAmt),CollectionAmt),0) as '>14Days',
if(Peek(DistParentId,-3)= DistParentId and Peek(WeekName,-3)< WeekName,peek(OS_Total,-3)- RangeSum(Peek(CollectionAmt,-2),Peek(CollectionAmt),CollectionAmt),0) as '>21Days',
if(Peek(DistParentId,-4)= DistParentId and Peek(WeekName,-4)< WeekName,peek(OS_Total,-4)- RangeSum(Peek(CollectionAmt,-3),Peek(CollectionAmt,-2),Peek(CollectionAmt),CollectionAmt),0) as '>28Days',
if(Peek(DistParentId,-5)= DistParentId and Peek(WeekName,-5)< WeekName,peek(OS_Total,-5)- RangeSum(Peek(CollectionAmt,-4),Peek(CollectionAmt,-3),Peek(CollectionAmt,-2),Peek(CollectionAmt),CollectionAmt),0) as '>35Days'
Resident Ageing_Bucket
Order By DistParentId, WeekName;

This code is giving the desired results. But I need some dynamic way so that if the user wants to add one more column in the bucket, then it can be done with no change in the script.

Thanks in Advance

Srashti Srivastava

Re: Want Dynamic Ageing Bucket

Gysbert_Wassenaar — Tue, 04 Nov 2014 08:19:36 GMT

See this blog post: True Dynamic Bucketing in QlikView – Infinity Insight Blog

topic Want Dynamic Ageing Bucket in QlikView

Want Dynamic Ageing Bucket

Re: Want Dynamic Ageing Bucket