sum of getpivotdata()

A

AlfredR

I have some excel pivottable connected to a cube. The data are further used
in excel by the getpivotdata() formula.

In the example below I have combined two getpivotdata() formulas to
calculate the Budget variance. As you see the only difference of the "budget"
and the "actuals" are the last element. "[Set].&[02.BGT]" vs
"[Set].&[01.ACT]").

EXAMPLE:
Actuals
=getpivotdata("[Measures].[Amount]";$A$6;"[TimeByYM]";"[TimeByYM].[Alle
MÃ¥neder]";"[Set]";"[Set].&[01.ACT]")


Budget
=getpivotdata("[Measures].[Amount]";$A$6;"[TimeByYM]";"[TimeByYM].[Alle
MÃ¥neder]";"[Set]";"[Set].&[02.BGT]")

Budget variance
=getpivotdata("[Measures].[Amount]";$A$6;"[TimeByYM]";"[TimeByYM].[Alle
MÃ¥neder]";"[Set]";"[Set].&[01.ACT]") -
getpivotdata("[Measures].[Amount]";$A$6;"[TimeByYM]";"[TimeByYM].[Alle
MÃ¥neder]";"[Set]";"[Set].&[02.BGT]")


In more complex cases when I have to use 6-10 getpivotdata() formulas it is
very difficult to read and maintain the formulas and mistakes may be done.

My Question:
=> Is it possible to make the formula shorter by using ony one
getpivotdata() to get the budget variance?

For instance somthing like this?

=getpivotdata(....;;;;;"[Set].&[01.ACT] - [Set].&[02.BGT]")

in other cases I would like the sum of two departments wich are not grouped
in the cube structure...
=getpivotdata(....;;;;;"[department].&[21] - [department].&[39]")

is this possible?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table grouping fields by Dept 1
Possible getpivotdata with offset? 1
sum of info in a range based on dates 4
Conditional SUM for Budget, Actuals, & Variance YTD columns 0
Query showing duplicate values 2
Add numbers with a DATE as one of the conditions 5
Sum every 6th cell in a row. 3
Sum Function that does not include 0 15

Top