Absolute Reference Calculation

We often use absolute references when we do calculations. Maybe other members are not aware of the option?

I have just answered a question from Keiron about the syntax. Below I will use his question as an example.

I have a crosstab with two product numbers "1010" and "1020" and I want to sum the two numbers and name it "Jeans sales".

Instead of using the formula: sum(0,d1,0,0) + sum(0,d2,0,0) I can type @"[1010]" instead of d1 and @"[1020]" instead of d2. The formula will then be: sum(0,@"[1010]",0,0) + sum(0,@"[1020]",0,0)

This way it will always reference to these to product codes, even if there are added more products to the table and ex product 1010 moves to third row (d3). 

If you have two or more row dimensions, you have to type it as @"[dimension1]";"[dimension2]" etc.

Ex sum(0,@"[1010]";"[Blue Jeans]",0,0) + sum(0,@"[1020]";"[Black Jeans]",0,0) for the example below:

If the two colums (product code and product descr.) was a hierarchi (ex product code level 1 in column 1 and product code level2 in column 2) you should just use a "." between the references instead, and just one set of "": sum(0,@"[1010].[Blue Jeans]",0,0) + sum(0,@"[1020].[Black Jeans]",0,0)

Of course you can also use the absolute references at your column dimensions. 

3

Comments

2 comments
  • Hi Louise

     

    You helped me with some calculation earlier so I will try again. If there was a third dimension after Product descr. for examples Sales Person and the table was a cross table with time (Month), is it possible, to make af sum for each month using absolute references?

    I have tried with something like sum(all,@"[1010]";"[Blue Jeans]",0,0) and hoping it wil take the sum for each month for all of the sales persons selling 1010 Blue Jeans. 

     

    I am aware that if I remove the third dimension with Sales Person, it will give me the sum, but I need to see how many sales persons, that a selling the product.

    0
  • Hi Jesper

    Unfortunately I don't think you can use a formula with absolute references to count the "children" directly.

    I would suggest:

    Make a new calculation: Tæl = count(0,all(s),m1)

    And then use the absolute references to refer to a random salesperson (Medarbejderkreds) in third dimension: Hankøn - Husass = avg(0, @"[Hankøn]";"[OVERENSKOMST FOR HUSASSISTENTER]";"[Overenskomstansat]", m2)

    I hope it makes sense?

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post