topic Aggr function help in App Development

Aggr function help

EllaCymraeg — Thu, 12 Jun 2025 14:57:38 GMT

Hello! We have this calculation that works:

if(Sum( {$<GLSubLedg.TransactionType = {"Payroll"}, GLSubLedg.ActualFlag = {'A'}, Currency = {"STAT"},[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum)-1)"},sum>} GLSubLedg.AmountNR)=0,

IF(Sum( {$<GLSubLedg.TransactionType = {"Payroll"}, GLSubLedg.ActualFlag = {'A'}, Currency = {"STAT"},[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum))"},GLSubLedg.PAY_Descriptor2={"*"}>} GLSubLedg.AmountNR)>0,

{<[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum))"}>} count(distinct([GLSubLedg.PAY_Descriptor2])),0),0)

but only when the column GLSubLedg.PAY_Descriptor2 is in the table. We would like to remove this column so that the data is summarised, but when we do that, the calculation returns zero. I think we need the AGGR function but everything I have tried has either returned zero, or returns the wrong answer. Can someone help with my syntax?

Re: Aggr function help

Chanty4u — Thu, 12 Jun 2025 17:19:07 GMT

Try this

If(

Sum(

{$<

GLSubLedg.TransactionType = {'Payroll'},

GLSubLedg.ActualFlag = {'A'},

Currency = {'STAT'},

[GLPeriod.PeriodMonthRP]=,

[GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum)-1)"}

GLSubLedg.AmountNR

) = 0,

If(

Sum(

{$<

GLSubLedg.TransactionType = {'Payroll'},

GLSubLedg.ActualFlag = {'A'},

Currency = {'STAT'},

[GLPeriod.PeriodMonthRP]=,

[GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum))"},

GLSubLedg.PAY_Descriptor2 = {"*"}

GLSubLedg.AmountNR

) > 0,

Count(

distinct

Aggr(

Only({$<

GLSubLedg.TransactionType = {'Payroll'},

GLSubLedg.ActualFlag = {'A'},

Currency = {'STAT'},

[GLPeriod.PeriodMonthRP]=,

[GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum))"},

GLSubLedg.PAY_Descriptor2 = {"*"}

>} GLSubLedg.PAY_Descriptor2),

GLSubLedg.PAY_Descripto

)

Re: Aggr function help

EllaCymraeg — Fri, 13 Jun 2025 07:44:18 GMT

Thank you for this, but again, this only works when the column GLSubLedg.PAY_Descriptor2 is in the table. I have tried to add the AGGR function to the two IF statements, but that then returns zero.