Conditional Partial Sums on Pivot Table

[#1]

I have a pivot table with two dimensions and several expressions. The primary dimension has multiple subdimensions in most cases (ex: Main dimension=Automobile, SubDimension=Car, Van, Bus, etc).

I have the subdimension set to partial sums in the pivot table, which works in most cases.However, there are some instances where the main dimension has only one subdimension. In THIS instance, we don't need a partial sum.

Is there any way to turn off partial sums for these instances?

[marcus_sommer]

It's only possible if you put your dimensions twice into the chart - one with partial sums and one without - and used visibilty-conditions on both columns, like: if(count(distinct Dimension) = 1, true(), false()) and reversed for the other one.

- Marcus

[ecolomer]

Is this you need?

See file attached (is an example pivot table with subtotals)

[#4]

Thanks for your help so far. I'm not sure these are the right approach.

Here's a visual of what I'm trying to do:

In the above example, "Grouping" and "Workgroup Name" are the primary and secondary dimensions. Our customers want a single pivot table that displays daily numbers for workgroups, each of which can be organized into a "Grouping," of which "All_Other/CLV" is an example.

At the top of the report, they want a Totals section, where many of the workgroups are added together to show totals (no, totals section at the top (ex: Overall Performance)  does not match the Grouping sections lower down (ex: All_Other/CLV)).

The problem I'm running into is I want the lower section of the report to show the grouping with the workgroups and a total at the top ... just like it does for the aqua section "All_Other/CLV." I accomplish this with partial sums on the Dimension "Grouping."

At the top (the purple Chat and up) I don't need the partial sum because there will always only be one workgroup. The numbers are repeated. I have a field that delineates between the two "types," so if I could do something like If type="totals" then hide partial sums ... .else show them, I'd be in business.

Is that possible?

[marcus_sommer]

This isn't possible directly within the pivot - the partial sums setting are global for a dimension if there are one value or more values. The easiest way to reach such layout would be to switch on the partial sums and hide the sub-values by using the plus- and minus-signs within the pivot - maybe with some further layout adjustments.

Another more complex solution could be to create a calculated dimension which counted the number of dimension values and if there are more then one add a total-row. In this case I would add this optional total-row within the script to avoid a dimension-creation with valuelist() and could use instead an simpler if-logoc and of course you will need to adjust your expressions with an if-loop to query if this row is a total or not.

- Marcus

[petter]

If you can be without the Pivot Table and switch to a Straight Table you could achieve what you asked for. But it takes a little bit more effort with more complicated expressions to achieve it. Visually you can get the same thing.

But I don't know if you need Pivot Table for some other reason and the Straight Table might not be an option for your other requirements?

[jonathandienst]

It is possible, buts its a little complicated to achieve.

You say you have a field to distinguish between two types, lets call them group1 (no subtypes) and group2 (with subtypes). I used a field, called LimitLevel, which had a value of 1 for group1 and 2 for group2. Then I used expressions of the form:

=If(Dimensionality() <= Max(LimitLevel) + 1, Sum(Amount))

This compares the dimensionality with the limit level to control the value returned. This way the sublevels return null for group1. You may need to adjust the expression for the number of dimensions in your table - in this case I had 3 dimensions, so group1 displayed down to dimensionality 2, and group2 down to dimensionality 3.

Make sure that suppress nulls is enabled.

I unfortunately cannot upload an example, but I hope my explanation will get you going.