topic Re: Identify which dimension is at each row level in App Development

Identify which dimension is at each row level

ali_hijazi — Tue, 25 Mar 2025 09:50:31 GMT

Hello I got the below pivot table:

the measure num_org is a simple sum with conditions in set analysis
the second measure denum_org is avg( aggr (sum(), Contract Type))

but since there are several dimensions to be selected and expanded what I want is the following:
for the row at the level of the Contract type I want the denum_org to be:
avg( aggr (sum(), Contract Type))
while for the row at the level of the contract :
avg( aggr (sum(), [Contract Type],Contract))

and so on
and also if the user swaps the dimensions I want the aggregation by to be based on the dimension which is at the level of the row

so if the user swaps Contract Type with Region then the expression at the level of the region should be:
avg( aggr (sum(), Region))

so how can I identify dynamically the dimension in the row of the pivot table if I"m at the level of Contracct type, Contract, or region,...

here if I want to get the result by contract I need to add Contract to the aggregation so the expression is
avg(aggr(sum(), Contract type, Contract))

However on the row at the level of "Internal" I want the calculation to be avg(aggr(sum(),Contract Type)) to keep the original value 5%

hope I clearly explained the issue
Kindly advise

Re: Identify which dimension is at each row level

AshutoshBhumkar — Tue, 25 Mar 2025 10:09:22 GMT

Hi, Check if you can use Dimensionality() function, which will help you to identify each row separately.

Thanks,

Ashutosh

Re: Identify which dimension is at each row level

ali_hijazi — Tue, 25 Mar 2025 10:30:09 GMT

dimensionality() helps me identify the level 1,2,3,...
what I want is to identify the dimension name so that I aggr by it

Re: Identify which dimension is at each row level

Chanty4u — Tue, 25 Mar 2025 17:14:43 GMT

Not tested give a try

Avg(Aggr(Sum(num_org), $(=GetObjectField(Dimensionality()))))

Re: Identify which dimension is at each row level

ali_hijazi — Wed, 26 Mar 2025 12:01:53 GMT

what I did is the following:
I put my expression in a variable to which I made the aggregation by column parameteric
and the expression in the chart looks like this and it works well

pick(wildmatch(GetObjectDimension(Dimensionality()-1)
,'Contract Type','Contract','act division','act department','act name','region','entity mgt','resource full name')
,$(vL.Rates.Denom.Group.By([Contract Type]))
,$(vL.Rates.Denom.Group.By([CNT]))
,$(vL.Rates.Denom.Group.By([ACT Division]))
,$(vL.Rates.Denom.Group.By([ACT Department]))
,$(vL.Rates.Denom.Group.By([ACT Name]))
,$(vL.Rates.Denom.Group.By([Region]))
,$(vL.Rates.Denom.Group.By([Entity MGT]))
,$(vL.Rates.Denom.Group.By([Resource Full Name]))

and the variable is as per below:
avg(
aggr
(
sum(
{
<
Scenario={[actual]}
,NMY={[$(vL.UpToDate)]}
,AGGREGATION_TYPE={ytd}
,MONTH_DIFF={$(vL.CY.MonthDiff.Actual.DeNumer.Range)}
,HCTYPE={[HC]}

>
}
HC)
,$1,[%PERIOD]))