Wrong total expression

[Facundo]

Hi,

I have a straight table showing top 10 values of a cyclic dimension using dimension limits with the following expressions :

- Sales : sum(Sales)

- Closed : sum(ClosedSales)

- % Pending : ("Sales" - "Closed") / "Sales"

But the Total of %Pending is wrong, it is ignoring the top 10 on dimension limits.

Any ideas why is this happening?

[swuehl]

AFAIR, that's expected, but I would also expect the first two expressions' totals to ignore the top 10 dimension limit unless you are using sum-of-rows total mode.

Could you post a small sample QVW?

[Facundo]

Yes, I'm using the sum-of-rows total mode.

Example attached.

[swuehl]

Instead of using dimension limits, you can use set analysis to filter TopX dimensional values, using something like

SUM({<[$(=GetCurrentField( Cyclic))] = {"=Rank(Sum([Total Sales]))<=2"}>} [Total Sales])

[vishsaggi]

Hello Stefan, if you wouldn't mind, Why the total should is 69.23 for Employee Dimension? In the Total mode we are using Sum of rows?

[Facundo]

Thanks Stefan, the example is working, but when I try to replicate on my qvw it doesn't works.

It fails when I try to add the following : 

SUM({<[$(=GetCurrentField( Cyclic))] = {"=Rank(Sum(

{<

[Date] = {">=$(=Date(AddMonths(MIN([Date]),-6),'DD/MM/YYYY'))<=$(=Date(AddMonths(MAX([Date]),-1),'DD/MM/YYYY'))"}

>}

[Total Sales]))<=2"}>} [Total Sales])

Any ideas why?

[swuehl · Accepted solution]

Probably because the pairs of (double) quotes doesn't really match to a valid syntax.

If your default date format is 'DD/MM/YYY' you can get rid of the explicite date format, and you can also make use of the 'kind of bug' that Qlik allows single quotes also around your (numeric) search:

SUM({<[$(=GetCurrentField( Cyclic))] = {"=Rank(Sum(

{<

[Date] = {'>=$(=Date(AddMonths(MIN([Date]),-6)))<=$(=Date(AddMonths(MAX([Date]),-1)))'}

>}

[Total Sales]))<=2"}>} [Total Sales])

[Facundo]

Great, that single quote trick is working, but my problem now is that I need the top 2 of a sum of two columns, to be more specific :

SUM({<

[Año Entrada]=,[Mes Entrada]=,[AñoMes Entrada]=

,[Fecha Entrada] = {'>=$(=Date(AddMonths(MIN([Fecha Entrada]),-6)))<=$(=Date(AddMonths(MAX([Fecha Entrada]),-1)))'}

,ESTADO = {'CERRADO'}

>}

GASTO_MEDICO)

+

SUM({<[Año Entrada]=,[Mes Entrada]=,[AñoMes Entrada]=

,[Fecha Entrada] = {'>=$(=Date(AddMonths(MIN([Fecha Entrada]),-6)))<=$(=Date(AddMonths(MAX([Fecha Entrada]),-1)))'}

,$(=$(v_campos_calendario))

,FECHA_CIERRE= {99991231}

,ESTADO = {'ABIERTO'}

>}

IMPORTE_MESA_ENTRADA)

How can I use the Rank function in this case?