When you insert a calculation in an object, you will always need to refer to the columns/rows/cells of the data bearing crosstab. Even if it is a chart - remember that you always have a crosstab "behind" the chart.
A calculation will always need a function as well - e.g. sum, avg, count etc.
function(column reference, row reference, measure reference)
Examples:
-
sum(d1, 0, m1)
-
avg(d-1, 0:-11, m2)
-
allcount(0, all, 0)
Comments
It would be helpful to explain what the arguments in the examples mean.
You are right Archie,
I have created an example crosstab with the sample arguments inserted as two calculated columns and one calculated row.
The basis for the crosstab are two measures: Revenue and No of Sales; three dimension columns: Asia, Europe and North America; 24 dimension rows: the 24 months.
Notice that Total and Subtotal columns/rows are "ignored" when counting columns/rows.
BR / Ole
Furthermore, this "cheatsheet" might be helpful.
Also attached as a downloadable PDF to the original article.
Resetting Running Totals: I'm using the "Accumulated Sum" standard calculation. I'd like to reset the accumulator when the value in Dimension (D1) changes. Any tips?
Hi Randy,
In my example above - the one with years and months on the vertical axis - if you want to reset your accumulation every time you get to a new year, you might try this:
sum(d1, all(s), m1)
BR / Ole
Please sign in to leave a comment.