Show expression totals for high level dimension, not at lowest dimension

[Anonymous]

Hi,

looking through something quite simple... then not that easy!

In a pivot table I have 3 dimensions, let say Year, Quarter and Month.

I have several simple expression A, B C,..., kind A = sum( myValue )

For Year and Quarter I have ticked the "Show Partial Sum" making totals to be shown for all my expressions.

I would like one of my expression not to show for Months, but only the totals at Year and Quarter level.

The other expression should still show for each month.

How can I do?

Many thanks in advance.

Thomas LK

[sunny_talwar]

So you want the month total to be blank or null? May be use Dimensionality()

How to use - Dimensionality()

The second dimension... or how to use secondarydimensionality()

[Anonymous]

Many thanks Sunny for your answer, but it doesn't match my need.

It is not "to be blank or null" I am looking for.

It is to Disable the expression at lowest dimension but still show partial sums of that expression on dimension above.

In the hereunder screenshot, I'd like expression highlighted in yellow not to display, as I'd like their total value (in green) still to be displayed.

Still I learned usefull things with Dimensionnality()!!! Many thanks for that!!!

Thomas

[sunny_talwar]

Would you be able to share a sample to look at this?

[Anonymous]

Well... I'll built an example by tomorrow.

Unfortunatly I cannot give the original, coming from screenshot, as it has confidential business data in... and it is a bit too big !

Thanks for your interest, and tries to help!!! Very agreable to have support...

Thomas LK

[Anonymous]

Hi again,

here is an example.

Attached QVW there is a Pivot Table. It has :

   - 1 primary dimension (Client) showing in rows

   - 3 secondary dimensions (Year, Quarter, Month) showing in columns

The show partial sums is activated for Month and Quarter.

It has 2 expressions: “Sum”, =sum(Amount), and “Avg”, =avg(Amount).

As it is:

Sum and Avg shows under each Month,

Sum and Avg shows under “Total”s at Quarter level as “Show Partial Sums” is active for Month,

Sum and Avg shows under “Total”s at year level as “Show Partial Sums” is active for Quarter.

I would like that:

Sum shows under each Month, but not Avg,

Sum and Avg shows under “Total”s at Quarter level as “Show Partial Sums” is active for Month,

Sum and Avg shows under “Total”s at year level as “Show Partial Sums” is active for Quarter.

Feel like doable?

Ma ny thanks in advance!

[sunny_talwar · Accepted solution]

This?

[Anonymous]

Look exactly what i expect!

I'll have a look at attached tomorrow... but I am confident it brings me the solution!

Many thanks!

Thomas LK

[sunny_talwar]

Sounds like a plan... have a good one

[Anonymous]

Many thanks Sunny!

Simple and smart, quite easy to understand... even could adapt with one more expression (count of...) at total level!

See attached (not for you Sunny, I guess you know how... but for others!)

Hope for other occasion to exchange!

Thomas LK