Referencing system-calculated pivot totals

[#1]

Hi,

I have an excel spreadsheet that I need to mimic in QV. The problem I'm having is that some of the totals I've achieved through Properties > Presentation > Show Partial Sums cannot be used in calculations. I've attached the QV file and the Excel file.

Please could someone help me to move towards what the Excel file is.

Thanks,

S

[Oleg_Troyansky]

In most cases, simple aggregate should be sufficient to cover both detailed levels and subtotals.

However, if you have to make some special calculations involving subtotals, you can use advanced Aggregation (function AGGR() ) and determine specifically what subtotal should be used in a specific calculation.

You might have to split your expressions to "detailed" and "totals" - in this case, function Dimensionality() helps determining the level of detail of the specific cell. Using Dimensionality(), you can create conditional expressions for each level of detail:

IF ( Dimensionality() = 0, ....

IF ( Dimensionality() = 1, ...

etc...

[#3]

Hi Oleg, thanks for the reply.

I've tried using the Dimensionality function but it's the first time I'm using it and I'm not getting it right. I understand what it does but I'm also having trouble inserting one expression after the Sub Total column. What happens is because the Invoice_Days is pivoted, one expression column is added for each invoice day. I've pasted an image below. What I would like to do is, working with the Total Column on the right, say: 82 / 165 and then 83 / 165.

Please could you help me to see how I can do this.

Thanks,

Shane

[Oleg_Troyansky]

Shane,

unfortunately you can't just add another expression after the Total - we don't have that kind of flexibility... The best you could do within a single chart is to replace the Total by a different calculation, conditioned with Dimensionality().

Another way, which is a but tricky, it to overlay multiple charts (Multiple Pivot Tables) and create an illusion of an additional field positioned after the Total. You'd have to ensure, however, that all the dimensions and other visual attributes are exactly identical. For example, you'd have to make your Pivot Table "Always fully expanded" because it's impossible to manage Collapsed/Expanded values across 2 pivot tables. This solution has many flaws, and we only use it when absolutely necessary.

A third way is to generate a "synthetic" dimension that would have all your Days, and then the "Total" and then the additional "expression" as Dimension Values, and create separate calculations for each one of them. This way, you are not truly using subtotals, but rather calculating everything manually based on the Dimension Value.

cheers,