Skip to Content

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.


Bucket 1 Header

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

Bucket 2 Header

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

Bucket 3 Header

=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”

Bucket 4 Header

=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”

Bucket 5 Header

= 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 6 Header

= “> “+[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

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Value])

3rd bucket column Header

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Header])

4th  bucket column Value

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Value])

4th bucket column Header

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Header])

5th  bucket column Value

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Value])

5th bucket column Header

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Header])

6th  bucket column Value

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Value])

6th bucket column Header

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Header])

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. ๐Ÿ™‚

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

    1. Fahad Shaikh

      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..

      Can someone please help me out with the variable.. For better understanding of the question kindly copy paste the table in excel and check it out, but please please help me out !!!


      Thanks In Advance ๐Ÿ™‚

      (0) 

Leave a Reply