0 Replies Latest reply: Aug 31, 2016 7:54 AM by Nikitha Jain RSS

    Implementing Agieng Bucket in Backend Script Using Excel

    Nikitha Jain

      Hi Friends,

       

      Currently I using the variables in front end to define the Ageing buckets and creating the expression in front end chart since the selection is As on Date. Now I want it be done in the backend script ,is it possible to do so.

       

      I have attached the  Bucket excel for reference.

       

      In the Front End I am using the expression like

       

      vBucketlabel1  is <=30 days

      =Floor( IF([Outstanding Amount ]=0,0,sum(If(
      ( (Billing.vAsOnDate-
      [Invoice Date])<=(vBucketEnd1)),[Invoice Amount ]))-
      Sum(If(
      [Receipt Date] <= (vMaxEndDate) AND
      ( (Billing.vAsOnDate-
      [Invoice Date])<=(vBucketEnd1)),[Receipt Amount ]))),0.01)

       

      where:

      Billing.vAsOnDate =  IF(IsNull(Billing.EndDate),Today(),Billing.EndDate)

      vBucketEnd1 = 30

       

       

       

       

      vBucketlabel2 is  31-60 Days

      =Floor( IF([Outstanding Amount INR]=0,0,Max(If([Invoice Date] <= (vMaxEndDate) AND
      ((Billing.vAsOnDate-
      [Invoice Date])>=(vBucketStart2) AND (Billing.vAsOnDate-[Invoice Date])<=(vBucketEnd2)),[Invoice Amount
      ]))-
      Sum(If(
      [Receipt Date] <= (vMaxEndDate) AND
      ((Billing.vAsOnDate-
      [Invoice Date])>=(vBucketStart2) AND (Billing.vAsOnDate-[Invoice Date])<=(vBucketEnd2)),[Receipt Amount
      ]))),0.01)

       

      where

      Billing.vAsOnDate = IF(IsNull(Billing.EndDate),Today(),Billing.EndDate)

      vBucketStart2 = 31

      vBucketEnd2 = 60

       

      Please suggest the suitable solution.

       

      Regards,

      Nikitha