topic Wrong total expression in QlikView

Wrong total expression

Facundo — Mon, 15 May 2017 20:49:09 GMT

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?

Re: Wrong total expression

swuehl — Mon, 15 May 2017 22:03:37 GMT

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?

Re: Wrong total expression

Facundo — Tue, 16 May 2017 14:13:44 GMT

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

Example attached.

Re: Wrong total expression

swuehl — Tue, 16 May 2017 15:49:50 GMT

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])

Re: Wrong total expression

vishsaggi — Tue, 16 May 2017 16:40:21 GMT

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?

Re: Wrong total expression

Facundo — Tue, 16 May 2017 18:38:39 GMT

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?

Re: Wrong total expression

swuehl — Tue, 16 May 2017 19:26:21 GMT

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])

Re: Wrong total expression

Facundo — Fri, 19 May 2017 17:46:08 GMT

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?