Referring to Calculated Dimension

[#1]

Hi

First post

I need help to improve an expression of mine...

I have at pivot table with one horizontal and one veritical dimension, both calculated (class intervals). Are there any way to reference the column/column value in expressions e.g.  in an IF or TOTAL modifier statement?

The wanted result should be something like SUM(NUMBER)/SUM (TOTAL <"Calculated Dimesion"> NUMBER) or an equivalent SUM(IF).

[Gysbert_Wassenaar]

You can't use calculated dimensions as field name when using the total modifier. You can only use real field names. In an if statement you can use the expression of the calculated dimension. You might want to put that expression in a variable in that case to make reuse easier.

[#3]

Hi and thanks for quick reply.

Problem is the calculated dimension are a class interval so the expression result would be 500 <= x < 550 which are not direct usable in an IF statement.

It also gets compared to all values in the SUM statement meaning that null or missing values in the data makes the expression returns a NULL. 

[#4]

Hello 🙂

I've been struggling with calculating the share % on a calculated dimension for a while myself, and just came up with this.  It seems to work so far in my pivot table:

sum(NUMBER)/ bottom(rangesum(above(sum(NUMBER),0,NoOfRows())))

Hope this works for you!

Karen

[#5 · Accepted solution]

Hi Karen

Thanks for replying in an old discussion...

It did not work entirely correct but you got me on the right track. You divide by the sum of rows i was looking for columns.

fixed it with following:

=SUM(NUMBER)/RangeSUM(First(TOTAL SUM(NUMBER),1,NoOfColumns()))

[#6]

Cool! ☺