2 Replies Latest reply: Apr 9, 2014 9:37 AM by Andrea Gigliotti

# pivot table relative percentage in expression

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 ?

Best regards

Andrea

• ###### Re: pivot table relative percentage in expression

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!

• ###### Re: pivot table relative percentage in expression

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