Aggregate a function when a certain criteria happens

Craig Blumberg

Hi,

I have a customer who has a statement in Coresuite. There are multiple lines each having a document date and a value attached. If you want to only add the aggregate sum when the document date is between 0 and 30 days from today. Then 31-60 days etc, so only add the invoices when certain criteria occurs.


I have tried this:

Use the IIF(Condition, true, false)
Condition: Check if your Date in the Range

Make 3 Aggegates

Name: SumTo30, SumTo60, SumTo90 .
Expression Sample:
0..30:

IIF(GetDayDif(GetData("B1_Data.Document.DueDate"))>=0 AND GetDayDif(GetData("B1_Data.Document.DueDate"))<=30,GetData("B1_Data.Document.Value"),0)

3160

IIF(GetDayDif(GetData("B1_Data.Document.DueDate"))>30 AND GetDayDif(GetData("B1_Data.Document.DueDate"))<=60,GetData("B1_Data.Document.Value"),0)

The Funtion GetDayDif can added to the common script of your report:

Function GetDayDif(DueDate As DateTime) As Double

Dim Days As Double = 0

Days = (DateTime.Parse(LD.Date(GetData("LD.Par.ToDate"),"dd.MM.yyyy")) - DueDate).Days

FROM TODAY: Days = (DateTime.Now - DueDate).Days

Return Days

End Function

However I cannot get it to work.

Craig

Philipp Knecht

Hi Craig

All Date Params coming from Parameters needs to be converted with the LD.ParamDate() Function.

try:

Days = (DateTime.Parse(LD.ParamDate(GetData("LD.Par.ToDate"),"dd.MM.yyyy")) - DueDate).Days

hth