topic Re: dynamic aggregation in App Development

dynamic aggregation

ali_hijazi — Fri, 12 Dec 2025 11:45:52 GMT

Hello
I have a pivot table with 3 dimensions (dim2, dim2, and dim3)
the expression uses a parameterized variable
the variable is vL.AMT
defined as aggr(sum(amount)/avf(sum(measure1),$(=replace('$1',';',',')),period)
if the value is at level first dimension then I do $(vL.AMT(dim1))
if the value is at level second dimension then I do $(vL.AMT(dim1;dim2))
...
it works fine when I pass the name of the dimension explicity (dim1,dim2)
however you know the user can change the order of the dimensions so dim1 won't be the first dimension
so I tried to do is use the GetObjectDimension(0) for level1, and GetObjectDimension(0);GetObjectDimension(1) for level2 etc...

however, it is not working; I'm getting null
kindly advise on how to pass the columns to aggregate on dynamically

Re: dynamic aggregation

BPiotrowski — Fri, 12 Dec 2025 12:59:10 GMT

Hi, @ali_hijazi

So I've tried to reproduce your situation and have made table with 3 dims
- Dept
- OfficeName
- ValueList

Measure is looking like this:
= if(Dimensionality() = 1,$(Dynamic(GetObjectDimension(0))),
if(Dimensionality() = 2, $(Dynamic(GetObjectDimension(1),GetObjectDimension(2))), $(Dynamic(GetObjectDimension(1),GetObjectDimension(2),GetObjectDimension(3)))
))

Variable body is:
If(Len($1) > 0 and Len($2) > 0 and Len($3) > 0, Count($1) + Count($2) + Count($3)
, If(Len($1) > 0 and Len($2) > 0 and Len($3) < 1, Count($1) + Count($2)
, If(Len($1) > 0 and Len($2) < 1 and Len($3) < 1, Count($1), 'NaN')))

My code is working so far.

Without sharing code it will be hard to help but I think here $(vL.AMT(dim1;dim2)) you should use , insted of ; $(vL.AMT(dim1,dim2)) between dims.

Re: dynamic aggregation

ali_hijazi — Fri, 12 Dec 2025 13:23:33 GMT

thank you @BPiotrowski
I will try it now
I used $(vL.AMT(dim1;dim2))
because there are other parameters that I need to pass
in the variable vLAMT it has $1,$2, and $3
dimensions are passed in the third parameter
if I put them comma separated the the call to the variable will like I'm passing 4 parameters
so I pass the third parameter as separated by ';' and inside the variable I replace the ; with , as follows:
aggr(...... , $(=replace('$3',';',',')))
it works when I pass the names of the dimensions explicitly

Re: dynamic aggregation

BPiotrowski — Fri, 12 Dec 2025 13:43:34 GMT

Does any of your Dimentions has space ?
Try adding brackets before sending $3 param.

Re: dynamic aggregation

ali_hijazi — Fri, 12 Dec 2025 14:12:41 GMT

Hello @BPiotrowski
it didn't work
Look I got the definition of the variable to the expression and it is as per below:
let me explain how it works
I'm checking the dimensionality and I say if it is 1 then expression with aggregation of GetFieldObject(0); if it 2 the aggregation by GetFieldObject(0),GetFieldObject(1)

but the result is null, but if I put the names of the dimensions explicitly it works

(

Dimensionality() <> 0

pick(Dimensionality(),fabs(

sum

(

{

Scenario={[actual]}

,NMY={[$(vL.Actual.UpToDate)]}

,AGGREGATION_TYPE={ytd}

,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.Numer.Range)}

,HCTYPE={[$(vL.Toggle.Rates.Movement)]}

,$(vL.Condition.Perm)

}

HC)

avg({<Scenario>}

aggr

(

sum(

{

Scenario={[actual]}

,NMY={[$(vL.Actual.UpToDate)]}

,AGGREGATION_TYPE={ytd}

,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.DeNumer.Range)}

,HCTYPE={[HC]}

,$(vL.Condition.Perm)

}

HC)

,$(=GetObjectField(0)),[%PERIOD]))

)

fabs(

sum

(

{

Scenario={[actual]}

,NMY={[$(vL.Actual.UpToDate)]}

,AGGREGATION_TYPE={ytd}

,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.Numer.Range)}

,HCTYPE={[$(vL.Toggle.Rates.Movement)]}

}

HC)

avg({<Scenario>}

aggr

(

sum(

{

Scenario={[actual]}

,NMY={[$(vL.Actual.UpToDate)]}

,AGGREGATION_TYPE={ytd}

,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.DeNumer.Range)}

,HCTYPE={[HC]}

}

HC)

,$(=GetObjectField(0)),$(=GetObjectField(1)),[%PERIOD]))

)

Re: dynamic aggregation

BPiotrowski — Fri, 12 Dec 2025 14:36:12 GMT

So thx for code use dimentions like this
avg(aggr(sum([A B]),[$(=GetObjectDimension(0))],[$(=GetObjectDimension(1))]))
I compresed the logic for tests but should help

Re: dynamic aggregation

ali_hijazi — Fri, 12 Dec 2025 14:46:42 GMT

yesss
it wokrs
guess what
I forgot that some dimensions have spaces in their names
I missed the [ ]
thank you very much @BPiotrowski