topic Re: Sum TOTAL with TOP N in QlikView

Sum TOTAL with TOP N

Facundo — Thu, 10 Nov 2016 17:23:45 GMT

Hi!, sorry for my bad english, I have a chart to display the top 5 VALOR_FIJO per FILIAL, but i need an expression to display the sum(total <FILIAL> NUM_CANTIDAD), but is always filtered for the top 5 of VALOR_FIJO, i've tried sum({<VALOR_FIJO=>} total <FILIAL> NUM_CANTIDAD) without success.

Pivot Table :

Dimension 1 : Filial

Dimension 2 : =aggr(if(rank(sum(NUM_IMPORTE_TOTAL_MAPA_CONTRATO))<=5,VALOR_FIJO),VALOR_FIJO)

Expression : SUM({<VALOR_FIJO=>} TOTAL <FILIAL> NUM_CANTIDAD)

The chart is displaying 24807, but the real number is 61196.

24807 is the total of the VALOR_FIJO displaying.

VALOR_FIJO	SUM(NUM_CANTIDAD)
0.00	8907
287.00	7708
71.50	4336
120.00	2363
196.00	1493

TOTAL : 24807

I can get the real value if i remove the <FILIAL> from the expression : sum({<VALOR_FIJO=>} total NUM_CANTIDAD)

But if I filter more than 1 FILIAL, I get the full total, and not the total of each FILIAL.

Any ideas of how can i achieve this?

Thanks!

Re: Sum TOTAL with TOP N

sunny_talwar — Thu, 10 Nov 2016 18:01:17 GMT

Would you be able to share your app to try out few things?

Re: Sum TOTAL with TOP N

Facundo — Thu, 10 Nov 2016 18:25:46 GMT

App shared

Re: Sum TOTAL with TOP N

sunny_talwar — Thu, 10 Nov 2016 19:01:45 GMT

Does this look right?

Re: Sum TOTAL with TOP N

Facundo — Thu, 10 Nov 2016 19:47:16 GMT

Thanks, that fixed the problem, but now i figured out that if you have selected just one FILIAL, you can see 5 values for that FILIAL, but if you have selected many FILIAL, you can't see 5 values per FILIAL!

Re: Sum TOTAL with TOP N

sunny_talwar — Thu, 10 Nov 2016 20:08:17 GMT

Check now

Re: Sum TOTAL with TOP N

Facundo — Thu, 10 Nov 2016 20:32:52 GMT

PERFECT!, thanks you very much!, it was driving me crazy.