dynamic aggregation

[ali_hijazi]

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

[BPiotrowski]

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.

[ali_hijazi]

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

[BPiotrowski]

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

[ali_hijazi]

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

if

(

    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]))

) 

)

)

)

[BPiotrowski · Accepted solution]

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

[ali_hijazi]

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