Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello I got a pivot table in which I want to calculate the investment sum and a ratio
suppose my expression for Investment is sum(measure)
and the expression of the ratio is sum(measure) / sum(total<Quarter, Investment Type,...> measure)
I have a problem in the calculation of the Ratio
when the dimensionality is 2 (Investment type) then the denominator in the expression of the Ratio should be
sum(measure) / sum(total<quarter> measure)
when the dimensionality is 3 like at the level of ISBM or NFR the ratio should be:
sum(measure / sum(total <quarter, InvestmentType>measure)
and when the dimensionality is 4 like where there is MX3 ISBM1.... the expression should be
sum(measure / sum(total <quarter, InvetmentType, InvestmentName> measure)
and so on...
kindly advise on how to accomplish this
The following didn't return the used dimensions in regard to the dimensionality() ?
pick(match(dimensionality(), 0,1,2),
GetObjectDimension(0),
GetObjectDimension(0) & ', ' & GetObjectDimension(1),
GetObjectDimension(0) & ', ' & GetObjectDimension(1)& ', ' & GetObjectDimension(2))
You may query the dimensionality and then branching into the wanted expression, maybe like:
sum(measure) / pick(match(dimensionality(), 0,1,2),
sum(total<quarter> measure),
sum(total<quarter, X> measure),
sum(total<quarter, X, Y> measure))
I tried this but the thing is that the order of dimension can be changed by the user
i also tried the GetObjectDimension function
it works without actually specifying the actual name of the dimension
However now I want to generate $(=GetObjectDimension($1)) dpending on the dimensionality
something I'm unable to accomplish
using the GetObjectDimension will make it easier when user changes the order of dimension,
but I need something to generate $(=GetObjectDimension(0)), $(=GetObjectDimension(1)) when Dimensionality() = 2 and so on
The following didn't return the used dimensions in regard to the dimensionality() ?
pick(match(dimensionality(), 0,1,2),
GetObjectDimension(0),
GetObjectDimension(0) & ', ' & GetObjectDimension(1),
GetObjectDimension(0) & ', ' & GetObjectDimension(1)& ', ' & GetObjectDimension(2))
well yes I was almost there