Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
Hi, Check if you can use Dimensionality() function, which will help you to identify each row separately.
Thanks,
Ashutosh
dimensionality() helps me identify the level 1,2,3,...
what I want is to identify the dimension name so that I aggr by it
Not tested give a try
Avg(Aggr(Sum(num_org), $(=GetObjectField(Dimensionality()))))
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]))