topic Re: Dynamice Total <> in a pivot table in App Development

Dynamic Total <> in a pivot table

ali_hijazi — Tue, 09 Apr 2024 11:13:48 GMT

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

Re: Dynamice Total <> in a pivot table

marcus_sommer — Tue, 09 Apr 2024 10:47:02 GMT

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

Re: Dynamice Total <> in a pivot table

ali_hijazi — Tue, 09 Apr 2024 11:12:43 GMT

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

sum

(

total<$(=GetObjectDimension(0)),$(=GetObjectDimension(1))>

{

SOURCE={MANDAYS_TIMES_DW}

,[Sub-Category]-={"*"}-{"is train","mx train"}

,[Consultant Division ID]={PRDCTDIV}

,DISPLAY_RECORD={1}

,[Epic/CBR Type]-={"Other","Program**Production Staging"}

}

MAN_DAYS

)

Re: Dynamice Total <> in a pivot table

marcus_sommer — Tue, 09 Apr 2024 11:31:36 GMT

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

Re: Dynamice Total <> in a pivot table

ali_hijazi — Tue, 09 Apr 2024 11:53:48 GMT

well yes I was almost there