How to get the TOTAL from a Top3 in a Straight Table

[#1]

Hi everyone,

I have a table like this

HOTEL	Noches	Precio	Media	rank([Noches],4,2)
E	5	490	98	1
D	4	400	100	2
C	3	200	66,6666666666667	3
B	2	180	90	4
A	1	100	100	5
TOTALES	15	1370	91,3333333333333

And I´d like to have a Top 3 table with TOTALS, where the Total of the 3º Column is the most important, cause it must be not the AVG of the values on the 3º Column, It must be (Sum of Total Values in 2º column) / (Sum of Total Values in 1º column).

So now I use the ranking to show only values when ranking is <= 3 and null when ranking isn´t, and I have this

HOTEL	Noches2	Precio2	Media2
E	5	490	98
D	4	400	100
C	3	200	66,6666666666667
A
B
TOTALES	12	1090

But Then I´d like to have something like this

HOTEL	Noches2	Precio2	Media2
E	5	490	98
D	4	400	100
C	3	200	66,6666666666667
TOTALES	12	1090	90,8333333333

How can I get it?

Thanks a lot

[#2]

Hi Eduardo,

did you check properties --> dimensions --> "Suppress when values is Null" ?

Regards, Roland

[#3]

Hi,

Yes, but the Only Dimension is 'HOTEL' and never is null.

Thanks a lot

[johnw]

Perhaps this to replace your current Media2 expression?

if(dimensionality()=0,column(2)/column(1),YourCurrentMedia2Expression)

I'm not certain the dimensionality of the total row will be 0, but I think it is. If so, then it's basically saying "if you're on the total row, calculate it by dividing the second column by the first column, otherwise calculate the value normally".

As for not seeing hotels A and B, rather than suppressing when the dimension is null, I believe you need to suppress when the expressions are null. Properties -> presentation -> suppress zero values and suppress missing.

[#5 · Accepted solution]

Hi everyone,

Thanks a lot for your answers.

I think I´ve solved the problem.

The big problem was that I can´t get a 'Total Expression' (in Total Mode) for the field 'Media2' if some of the Expresions I use in 'Media2' doesn´t have a right value in his 'Total Expression'.

The 'Media2' Expression was something like this [Precio]/[Noches], but I dind´t know how to get a right value in TOTALS for this fields.

So now I have been able to get a right value in all Total Expressions for all my fields using the function aggr.

Now I have in Expressions [Precio] and [Noches] something like this:

SUM(
aggr(
if(
rank(sum(Noches),4)<=3
,sum(Noches)
)
, [HOTEL])
)

So I can use the Total Expressions in Total Mode without problems.

I attach my example.

Thanks a lot