Want Dynamic Ageing Bucket

[#1]

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

[Gysbert_Wassenaar]

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