pivot table relative percentage in expression

[agigliotti]

Hello,

I have a pivot table with 3 dimensions.

I need to achieve the relative function as the option for expression in straight table but using pivot table.

I solved it using the IF statement with DIMENSIONALITY, take a look below:

If( Dimensionality() = 1,

  sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} total [Ore lavorate]),

If( Dimensionality() = 2,

  sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} Total <[Gruppo attività]> [Ore lavorate]),

If( Dimensionality() = 3,

  sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} Total <[Gruppo attività], [Gruppo attività L6]> [Ore lavorate]) ) ) )

Is there a way using a single expression without the needs of IF statement to achieve the same results ?

Thank you in advance for your time.

Best regards

Andrea

[jerem1234]

You could use the pick function like:

pick(dimensionality(),

sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} total [Ore lavorate]),

sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} Total <[Gruppo attività]> [Ore lavorate]),

sum( {< [Anno movimento] = {'$(=Year(today())-2)'}, [Data MMGG] = {">=$(vMMGGdal)<=$(vMMGGal)"} >} Total <[Gruppo attività], [Gruppo attività L6]> [Ore lavorate])

)

Hope this helps!

[agigliotti]

Yes the expression syntax is better for sure but I'm looking for a unique expression and not one expression for each dimension.

However many thanks for your fast answer.