Two expressions - for the second, show only totals

[salto]

Hello,

I have a pivot table in which I show the P&L for the current year, and for the previous year, per month:

Ok, not bad until now. The requirement is that I would need the '2013' column to show only the totals, not the values for every month. The % variation ONLY for the totals would also be fantastic to have...

Is this possible? Many thanks in advance!

[kristoferahlin]

Unfortunately, I think the only way is to create one expression per month, one expression for 2013, one for 2014 and one for variation (%).

I have been wrestling with the same problem myself and look forward to being proven wrong

Cheers,

Kristofer

[salto]

Thank you Kristofer, will do it with the workaround you have suggested.

Cheers!

[Anonymous]

How about setting the expression text colour to white on a white background with a suitable expression using set analysis, an if statement and the dimensionailty() function.

[salto]

Thank you Bill.

I had a look at the dimensionality() function but in my chart, for every month and for totals, the value of dimensionality() returns 1 in all cases.

And, even if the dimensionality functions returned a different value (let's say,1 for totals and 2 for every month), would it be possible to hide the whole columns in which dimensionality returns 1 (including the heading and not only the values), instead of changing the text colour and background to white?

Many thanks again for your suggestion.

[tresesco]

Not sure if understood right. If you want same total across months, try with 'Total' something like: Sum( Total Amount) . If that doesn't help, try to share your expected output.

[salto]

Hello tresesco,

this is what I had, using Description and Month as Dimensions, and two Expressions:

The expressions are:

      =num(sum({<Year={$(=max(Year))}>} MonthlyAmount), '#.##0 $(vCurrency)')

and

     =num(sum({<Year={$(=max(Year)-1)}>} MonthlyAmount), '#.##0 $(vCurrency)')

I would like, for the second expression, to show only the totals.

I managed to do it removing the Month field from the Dimensions, and adding :

• a expression for every month in max(Year),
• other expression for Totals in max(Year),
• other for Totals in max(Year)-1
• and other one for Var(%).

This is what I wanted and what I got, following Kristofer's suggestion:

It works, but I had to play a bit with getselectedfields(Month).

The question was if this could be directly achieved without adding a expression per month, making the second expression to show only the totals.

Regards!