Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

Hi everyone,

I have a table like this

HOTEL Noches Precio Media rank([Noches],4,2)
E5490981
D44001002
C320066,66666666666673
B2180904
A11001005
TOTALES15137091,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



E549098



D4400100



C320066,6666666666667



A






B






TOTALES121090




But Then I´d like to have something like this

HOTEL Noches2 Precio2 Media2



E549098



D4400100



C320066,6666666666667



TOTALES12
1090
90,8333333333




How can I get it?

Thanks a lot

1 Solution

Accepted Solutions
Not applicable

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

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

4 Replies
Not applicable

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

Hi Eduardo,

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

Regards, Roland

Not applicable

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

Hi,

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

Thanks a lot

MVP
MVP

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

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.

Not applicable

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

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

Community Browser