Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Eduardo,
did you check properties --> dimensions --> "Suppress when values is Null" ?
Regards, Roland
Hi,
Yes, but the Only Dimension is 'HOTEL' and never is null.
Thanks a lot
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.
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