Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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.