topic Re: Re: add total row (Calculated) in QlikView

add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 14:48:09 GMT

Hi,

Searching into the forum I found an app from jagan who explain how to add a calculated row. I tried to do the same thing with my app but the total appears in the column: I would like sum top 3 elements. I dont want include ADMINISTRACIÓN. I attached the Jagan Sample and my app. Can you explain me how to do this... because total in my app appears in column.

Departamento	Ene	Feb
1.INGRESOS DE LA EXPLOTACIÓN	750.962.418	400.996.625
2.COSTO DE VENTAS	-503.155.054	-267.907.385
3.OTROS INGRESOS DE LA EXPLOTACIÓN	5.936.236	446.977
4.REBATE PROVEEDORES	0	-7.000.000
Total	253.743.600	126.536.217
ADMINISTRACIÓN	-51.136.540	-31.918.623

Thanks!!

Re: add total row (Calculated)

christian77 — Mon, 25 Aug 2014 14:56:21 GMT

Hi.

Pivot tables totalize by the general expression.

In pressentation tab, check show partial sums, then it'll be automatic.

If the general expression does not give you the desired result, go like:

if (dimensionality()=0, Your Total Expression, Your Expression)

dimensionality()=0, means totals.

Pivot table with pivoted dimensions use also SecondaryDimensonality().

See dimenssionality in the help menu.

If you have all those expressions, just add them all.

Good luck.

Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 14:57:47 GMT

Is it just this that you want ?

Re: add total row (Calculated)

christian77 — Mon, 25 Aug 2014 15:01:24 GMT

Hi again.

I think you need to dimension by Periodo.

Añade la dimensión periodo y luego chequea la casilla mostrar sumas parciales como te digo en el primero.

Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 15:07:18 GMT

Hi Chris thsnk for you reply,

But in the jahan app doesn´t use dimensionality, if so, how it wold be the final expression?

if (dimensionality()=0, Column(1) + Column(2) ??+, Your Expression)

Thanks!

Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 15:08:50 GMT

Hi Jonathan,

Is not what I want I just want obtain the total of

1.INGRESOS DE LA EXPLOTACIÓN	750.962.418	400.996.625
2.COSTO DE VENTAS	-503.155.054	-267.907.385
3.OTROS INGRESOS DE LA EXPLOTACIÓN	5.936.236	446.977
4.REBATE PROVEEDORES	0	-7.000.000

I dont want to include ADMINISTRACIÓN

Re: add total row (Calculated)

alematex — Mon, 25 Aug 2014 15:09:43 GMT

His expressions are rows, yours are columns, thats why you can´t obtain the same.

Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 15:14:09 GMT

Hi Alejandra I know that, But how could obtain that? I can't figure out. I just have the similar scenario. One dimension and expressions.

Thanks!

Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 15:17:59 GMT

Got it. I missed that in your first post.

One option which isn't extremely dynamic is to create a new field in the data model with a sort number for each value.

There would be some maintenance if the values change, but that would be the case for a chart based solution as well:

PivotTable:

load

cc_group_desc,

cc_group_desc as PivotTableDimension

if( cc_group_desc='Administration',6, left(cc_group_desc,1)) as SortNumber

resident <yourothertable>;

concatenate (PivotTable)

load

cc_group_desc,

'Total' as PivotTableDimension

5 as SortNumber

resident <yourothertable>

where

cc_group__desc <> 'Administration';

Use PivotTableDimension in the chart instead of cc_group_desc and sort on only(SortNumber) on the sort tab.

Re: Re: add total row (Calculated)

christian77 — Mon, 25 Aug 2014 15:24:49 GMT

Ok, ther's a little problem in your model.

Anyway here is one solution.

about your model, I would cross those tables and also concat them. They have many fields in common

Re: Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 16:00:30 GMT

Jonathan, almost total look very well in the position but the total appears = 0 I attached the final app with your changes.

Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 16:22:16 GMT

add the new pivottabledimension to the aggr as well:

Sum(Aggr(

If(cc_group_desc='1.INGRESOS DE LA EXPLOTACIÓN' or cc_group_desc='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or cc_group_desc='4.REBATES',Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

//Sum(ppto_clp_Real_01)

Re: Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 16:51:25 GMT

If I left just Sum(ppto_clp_Real_01). Total is good, but the other values have changed. If I change the dimmension by yours total = 0. Can you edit my app for better comprehenssion. I think I missing something.

Thanks.

Re: Re: Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 17:01:45 GMT

ok here is everything . my previous post i suggested 'solution2' below. In your example, i tweaked it a little to create solution1.

I think either will work for this sample. do some more testing and see which one you are comfortable with.

Solution 1:

Sum(Aggr(

If(PivotTableDimension='1.INGRESOS DE LA EXPLOTACIÓN' or PivotTableDimension='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or PivotTableDimension='4.REBATES' or PivotTableDimension='Total' ,Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

Solution2 :

Sum(Aggr(

If(cc_group_desc='1.INGRESOS DE LA EXPLOTACIÓN' or cc_group_desc='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or cc_group_desc='4.REBATES' ,Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

Re: Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 17:27:01 GMT

Jonathan your solution before was the correct. I apologize my mistake because 2014 wasn't selected that is the reason

1.INGRESOS DE LA EXPLOTACIÓN

750.962.418

400.996.625

If I select Año 2014 appears the correct result.

One more thing Jonathan in my app I change the text format in the tab expression drill down sum sign

Teext Format:

=If(Grupo='MARGEN DE EXPLOTACIÓN' or Grupo='TOTAL COSTO DE ADMINISTRACION Y VENTAS' Or Grupo='TOTAL RESULTADO DE LA EXPLOTACIÓN' or Grupo='TOTAL INGRESOS FUERA DE LA EXPLOTACION'

Or Grupo='TOTAL EGRESOS FUERA DE LA EXPLOTACIÓN' or Grupo='TOTAL RESULTADO FUERA DE LA EXPLOTACIÓN'

Or Grupo='RESULTADO ANTES DE COSTO DE ADMINISTRACION' Or Grupo='TOTAL RESULTADO ANTES DE IMPUESTO' Or Grupo='RESULTADO EJERCICIO','<B>',Grupo)

So, total was in bold text. How can I left total like that (Bold Text) Everything the total and the numbers of the totals.

Thanks for your help.

Re: Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 17:37:22 GMT

i don't know if i understood 100%. but to BOLD the 'Total' row i did this:

Re: Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 17:48:04 GMT

Total appears now with bold but the total numbers not.

Re: Re: add total row (Calculated)

JonnyPoole — Mon, 25 Aug 2014 17:59:07 GMT

Pablo, go to the expressions tab and expand the expression the same way you did in the dimensions tab.

Put the same expression in the 'text format' as above .

You need to do it for each expression.

Re: Re: add total row (Calculated)

pgalvezt — Mon, 25 Aug 2014 18:05:11 GMT

Many thanks Jonathan.