Custom Total Calculation in Straight table

[MKhafaga1]

Hi all,

I am new by Qlik Sense and facing some difficulty with an expression.
I have a straight table visual containing the following columns 

1) Rank ( Measure only limiting to top 10 )                    2)CustomerName (Dimension)

3)GSV_USD (Measure using Current year Revenue)

4) GSV_PY (Measure using last year Revenue )

When I add a 5th column to show Delta CY/PY % ; expression: Column(CY) - Column(PY) / Column(PY) ,

on rows level calculation is fine, but for total it is limited with the drop down options and when selecting SUM , it gives the sum of all column rows instead which is not correct, I target having something like the sum(column(CY)) - sum(column(PY) / Sum(column (PY)) keeping the context of the table (totals only for the top 10 customers)

Current Delta% Measure looks like :
if(dimensionality()=1,Column(2) - Column(4) / Column(4)  , Missing Expression!! )

Appreciate ur advice

[rubenmarin]

Hi, maybe using a caulated dimension to limit the top 10, as:

=AGGR(If(Rank([RankExpression])<=10,CustomerName),CustomerName)

[MKhafaga1]

That would not solve the issue , i get '' -  '' as a result

[rubenmarin]

Did you set your rank expression on "Rank([RankExpression])"?

Btw, that was to use CustomerName as a calculated dimension, limiting the values that way you can use the default 'Automatic' option for totals.

[MKhafaga1 · Accepted solution]

I solved it this way:

if(dimensionality()=1,[delta PY], (GSV_Top10_CY - GSV_Top10_PY) / GSV_Top10_PY)

Where The measures are as following:

GSV_Top10_CY  = Sum(Aggr(If(Rank(Sum(GSV_USD), 1, DESC) <= 10, Sum( GSV_USD)),CustomerName ))

GSV_Top10_PY = Sum(Aggr(If(Rank(Sum(GSV_USD), 1, DESC) <= 10, Sum( FYYear ={"$(=Max(FYYear)-1)"}>} GSV_USD)),CustomerName))

[rubenmarin]

Hi, that's another option, the difference is that you have to set the rank condition in each measure.

If you set the condition on the CustomerName dimension you can avoid to set it on all the expressions.

=AGGR(If(Rank(Sum(GSV_USD), 1, DESC)<=10,CustomerName),CustomerName)

For only 2 expressions, it doesn't really matter.