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