With the TARGIT Designer client, you can add client-based calculations to your data objects.
You do this from the 'Calculations' Smartpad in your TARGIT client:
The Calculations Smartpad offer three types of calculations:
- Pre-defined calculations. In example above, from 'Total' to 'Rank (Ascending)'. These calculations can be added to any selected object for immediate assessment. The results may vary, depending on the number of measures and dimensions in the selected object.
- Advanced calculations. This will open the objects in a calculation editor where the calculation syntax can be resolved by clicking individual columns and rows directly in the crosstab. See "Advanced" calculations - the 'point and click' editor.
- Custom calculation. When you select this option, you will need to type in the full calculation syntax yourself. Most of the articles in the Calculations section are related to the custom syntax. Below, you may read a short introduction to this syntax.
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)
Furthermore, notice the attached 'Calculations cheat sheet' to this article.
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.