- Add another crosstab looking at Revenue per Salesperson, by Product Hierarchy(Product Group) and by Customer(Territory).
Now make sure that all 3 dimensions are on the same (vertical) axis as shown here:
This creates a special situation in relation to the level parameter. You can actually reference the level of each dimension in one sentence.
This example references the totals of the Salespersons:
Salespersons totals: sum(d-1, 0(l(1, 0, 0)), m1)
l(1, 0, 0) means level 1 on the Salesperson dimension and level 0 on Product and Customer Country (level 0 being the “all” level).
So in short – totals for Products and Customer Country but still within each Salesperson.
To reference the Product subtotals the syntax would be:
Product Subtotals = sum(d-1, 0(l(1, 1, 0)),m1)
Totals on the Customer Country level – but within each Salesperson/Product Group.
Named reference in combined heirarchy
In the above example, in case you needed to refer to a specific cell, using the “@” reference method, the syntax should be:
sum(d1, @”[Alvaro Bennett]”;”[T-SHIRTS]”;”[Europe]”, m1)
Note: Even if one of these levels is hidden, e.g., the Product Group level, it should still be included in the named reference syntax.
Comments
Hi,
Is this possible to do with the average function, rather than the sum?
I tried using the following: avg(d-1, 0(l(1,0,0)), m3)
But it returns the sum rather than the average.
Hi Lara,
Your syntax, avg(d-1, 0(l(1,0,0)), m3), indicates that you are calculating the average of the current row - i.e. the average of a single cell is identical to the sum of a single cell.
If your average calculation should make sense, it should probably be one of either:
BR / Ole
In this example, would it be possible to calculate the subtotal of Jeans across Salespersons?
Something like sum(d-1, 0(l(0, 1, 0)),m1)? This does not work out for me...
BR Michala
Hi Michala,
You can do that, but you will need to work with 'Multidimensional subtotals'.
See this article: https://community.targit.com/hc/en-us/articles/360017917778-Multidimensional-Subtotals
BR / Ole
Thanks, Ole.
I can't crack the code. My table contains 6 separate dimensions as seen below. I want to calculate the average "Sales price EUR" per MPN (1, 2, 3,4 and 5), and I want to show it in the column "Avg Sales price" for each line.
How do I do that?
BR Michala
Hi Michala,
I am sorry, but I don't think it will work for you. The Multidimensional subtotals are only good for giving you exactly that: The *total* of every dimension member in a mixed hierarchy, independent of its current level.
To transfer it into something useful in a table where it should show the correct multidimensional sum (or the average, for that sake) for each corresponding Product Group (or corresponding MPN in your case) would be impossible, I am afraid.
BR / Ole
Hi Ole,
Okay - too bad. Thanks for your time :-)
Hi Targit people,
You should mention this works only if the regarding hierarchy sums are shown.
If not, the formula returns 0. That's a bit of a nuisance.
It is also kind of mean that the program first shows the same result when I deactivate the hierarchy sums and save it, and when I open the report again, whoopsie daisy, it is all 0 again. The only way to get that working is by removing the visibility for each hierarchy sum I do not want in the table.
Cheers,
Florian
Please sign in to leave a comment.