 # Dynamic Ageing Buckets in Webi

Hi Team,

We’ll be asking for dynamic aging buckets many times at customer places. Each user want to see different aging intervals ,so forced to do multiple copies of  same BEx query with different interval for buckets so do Webi report.

I have got Order Number ,Invoice Number ,Aging Days (Difference between 2 dates Due date and current date)  and Outstanding Amount objects from backend (BEx query).

Logic behind : We’ll be having 6 buckets (Number of buckets are fixed) and will change range  of buckets dynamically with 5 input controls entered by user.

Process:

Create 5 Variables with default values which will act as Input controls.

Bucket1 : Measure variable :=15(default value , so at first we’ll get 0-15 range of bucket)

Bucket2 : Measure variable :=15(default value , so at first we’ll get 16-30 range of bucket)

Bucket3 : Measure variable :=15(default value , so at first we’ll get 31-45 range of bucket)

Bucket4 : Measure variable :=45(default value , so at first we’ll get 46-90 range of bucket)

Bucket5 : Measure variable :=90(default value , so at first we’ll get 91-180 range of bucket)

The last bucket we’ll be the >([Bucket1]+…+[Bucket5]) bucket ,which is >180 .

Add Bucket1..Bucket5 variables input controls with Entry Field selection . Then create 4 variables to get sum of buckets to add up dynamically.

Bucket 1+2                         =[Bucket 1]+[Bucket 2]

Bucket 1+2+3                     =[Bucket 1]+[Bucket 2]+[Bucket 3]

Bucket 1+2+3+4                 =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]

Bucket 1+2+3+4+5             =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]+[Bucket 5]

Now derive values for bucket ranges .Since we are not showing/using Invoice number and Order number in the Webi table .I have included Invoice number and deal number in calculation context. You can remove them when you are working on with your requirement.

Bucket1 value

=Sum([Outstanding Amount] Where ([Ageing Days] Between(0;[Bucket 1])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 2 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then  Sum([Outstanding Amount] Where ([Ageing Days]>[Bucket 1])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1]+1;[Bucket 1+2]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 3 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2])  Then Sum([Outstanding Amount] Where ([Ageing Days]  >[Bucket 1+2]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days]  Between([Bucket 1+2]+1;[Bucket 1+2+3])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 4 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3]+1;[Bucket 1+2+3+4])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 5 Value

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3+4])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3+4]+1;[Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 6 Value

=Sum([Outstanding Amount] Where ([Ageing Days] >([Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Now ,derive Buckets headers without this the report is not meaningful.

=0+”-“+[Bucket 1] +” Days”

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then “> “+[Bucket 1] +” Days” Else [Bucket 1]+1+”-“+[Bucket 1+2]+” Days”

=If([Bucket 1+2+3+4+5]=[Bucket 1+2]) Then  “> “+[Bucket 1+2]+” Days” Else [Bucket 1+2]+1+”-“+[Bucket 1+2+3]+” Days”

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then  “> “+[Bucket 1+2+3]+” Days” Else [Bucket 1+2+3]+1+”-“+[Bucket 1+2+3+4]+” Days”

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then  “> “+[Bucket 1+2+3+4]+” Days” Else  [Bucket 1+2+3+4]+1+”-“+[Bucket 1+2+3+4+5]+” Days”

= “> “+[Bucket 1+2+3+4+5]+” Days”

(This part is optional , we can leave blank columns also.)

The last part is to make the columns shrink/hide when there is 0 value specified as bucket value.

Since anyhow we’ll get Bucket1 and bucket2 value (If at least Bucket1 value is specified ) write formula to hide remaining columns and make them auto width with 0 inch . 3rd bucket column Value

4th  bucket column Value

5th  bucket column Value

6th  bucket column Value

Caution : Don’t implement this logic if report brings more than 10k rows at view time .This can kill performance.

We can follow similar logic to give bucket range at @prompt level too. But the burden on Webi is same.

Thanks to Sabari Vasan. S for seed of this tip.

Hope somebody will find it helpful. Feel free to suggest changes in logic /other way of doing this. 🙂

### Assigned Tags

You must be Logged on to comment or reply to a post. Hi Sateesh. Good tip Hi Sathish,

can you combine this approach with the method described inUsing Nested Exception Aggregation in BEx Reports- Scenario in order to push the calculation to BW? This might  improve the performance.

Best Regards,

Clemens Sateesh Kumar Bukkisham
Blog Post Author

Hi Clemens ,

It can be achievable in BEx with Customer Exits.I'm sure it'll be bit difficult. Great Webi trick !

You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

William Respected All;

I am stuck in a variable for couple of weeks.

Please help me to get the difference in dates coming under the Ac Create Date Column for respective Request Id's.

I want the result in the Delay Column (Variable Required for the result of delay).

For eg:-

 A B C D E F G Request Id Ac Create Date Act Role Proper Name Delay Simplified(Variable Required Delay Act Activity Name 1 000000000560492 7/9/2014 Debt Recovery - Archive =Ac Create Date C2 - Ac Create Date C1 5 Resolved 2 7/14/2014 Call Centre - Back Office =Ac Create Date C3 - Ac Create Date C2 1 Forward 3 7/15/2014 OWN Shop - AL Rayyan =Ac Create Date C4 - Ac Create Date C3 7 Resolved 4 7/22/2014 Call Centre - Back Office = Ac Create Date C5 - Ac Create Date C4 0 Customer Feedback 5 7/22/2014 Call Centre - Back Office "NA" NA Closed FC 6 000000000561010 7/1/2014 Call Centre - Back Office =Ac Create Date C7 - Ac Create Date C6 1 Forward 7 7/2/2014 FOLLOW UP TEAM =Ac Create Date C8 - Ac Create Date C7 0 Resolved 8 7/2/2014 Call Centre - Back Office =Ac Create Date C9 - Ac Create Date C8 1 Forward 9 7/3/2014 R_RO =Ac Create Date C10 - Ac Create Date C9 12 Return - Wrong Assignment 10 7/15/2014 Call Centre - Back Office =Ac Create Date C11 - Ac Create Date C10 1 Forward 11 7/16/2014 IT CRM & Provisioning =Ac Create Date C12 - Ac Create Date C11 0 Reassignment 12 7/16/2014 IT CRM & Provisioning =Ac Create Date C13 - Ac Create Date C12 1 Follow Up 13 7/17/2014 IT CRM & Provisioning =Ac Create Date C14 - Ac Create Date C13 0 Reassignment 14 7/17/2014 IT_Comptel =Ac Create Date C15 - Ac Create Date C14 3 Resolved 15 7/20/2014 Call Centre - Back Office =Ac Create Date C16 - Ac Create Date C15 0 Recategorization 16 7/20/2014 Fiber Call Center-Back Office =Ac Create Date C17 - Ac Create Date C16 0 Forward 17 7/20/2014 IT CRM & Provisioning =Ac Create Date C18 - Ac Create Date C17 1 Resolved 18 7/21/2014 Fiber Call Center-Back Office =Ac Create Date C19 - Ac Create Date C18 0 Resolved 19 7/21/2014 Fiber Call Center-Back Office =Ac Create Date C20 - Ac Create Date C19 0 Customer Feedback 20 7/21/2014 Fiber Call Center-Back Office "NA" NA Closed

I have used Delay =DaysBetween([Ac Create Date];Previous([Ac Create Date]))

but however I'm not getting the results required.. Sateesh Kumar Bukkisham
Blog Post Author

Thank you William , Awesome logic 😀 Dear Sateesh Kumar

as you mentioned I need to have "Aging Days (Difference between 2 dates Due date and current date) "

I have Due Date, but I did not have the date desired for the cutoff date. How to get this date?

Regards Sateesh Kumar Bukkisham
Blog Post Author

Hi Cruz ,

if report is for as on today ,then create variable

today=currentdate()

otherwise you may need to consider different date column as per business requirements .

Thanks