topic Re: Sum in a pivot table in App Development

Sum in a pivot table

sebastian_marti — Wed, 11 Apr 2018 19:11:22 GMT

Hi, in a pivot table I have one field with values 1, 2 and 3.

I want to divide one amount field by SUM(amount) when field value = 1, for each value (1,2,3).

Description	Totals	% Sales
Sales	1000	100%
Cost	500	50%
Other Cost	200	20%

Thanks in advance

Sebastian

Re: Sum in a pivot table

sunny_talwar — Wed, 11 Apr 2018 19:39:07 GMT

May be this

Sum(Value)/Sum(TOTAL {<Description = {'Sales'}>} Value)

Re: Sum in a pivot table

sebastian_marti — Wed, 11 Apr 2018 19:43:37 GMT

Wonderful!

It's works!

Thank you

Re: Sum in a pivot table

sebastian_marti — Wed, 11 Apr 2018 20:29:23 GMT

In a pivot table, "Totales" column is the total amount of Account. % Sobre Venta is Totales divided by Totales when Account = "Ingresos".

When two years are choosen, the % value is distributed across each year, example: in "Ingresos" line, % values are 16.84% and 83.16%, both sumarized values totals 100%.

I need that each value represent the % belongs its year. In this case, both column must be 100% (100% for 2017 and 100% for 2018).

The table looks like this:

I really appreciate your support.

Best regards,

Sebastián Martínez

Re: Sum in a pivot table

sunny_talwar — Wed, 11 Apr 2018 20:56:28 GMT

Then try this

Sum(Value)/Sum(TOTAL <YearField> {<Description = {'Sales'}>} Value)

Re: Sum in a pivot table

sebastian_marti — Wed, 11 Apr 2018 21:12:53 GMT

It works!!!

Thanks a lot!

Re: Sum in a pivot table

sebastian_marti — Thu, 12 Apr 2018 16:50:59 GMT

Dear Friend,

I need your help one more time.

I got this problem. I made a difference between two years but i want to show the measure in the end of the table.

I need this solution: (paint)

Please help me!

Re: Sum in a pivot table

sunny_talwar — Thu, 12 Apr 2018 17:25:44 GMT

May be approach it like this

Chart Expression | Qlik Community

Re: Sum in a pivot table

sebastian_marti — Thu, 12 Apr 2018 18:49:43 GMT

Hi Sunny, in that link you included one file called "GSD_Value_Scorecard_v1.qvw" but is impossible to open it.

Could you explain in simple words how it works the function/formula?

Thanks,

Re: Sum in a pivot table

sebastian_marti — Thu, 12 Apr 2018 21:39:34 GMT

Isn't work because the colum still appear in 2018.

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 02:30:22 GMT

I am not entirely sure what you mean, would you be able to share a sample?

Re: Sum in a pivot table

sebastian_marti — Fri, 13 Apr 2018 12:16:09 GMT

When I make the KPI (difference between two years) I got a table like this:

But, I need show this table like this:

(This image I edited in paint)

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 12:17:36 GMT

What is the expression you used?

Re: Sum in a pivot table

sebastian_marti — Fri, 13 Apr 2018 12:31:52 GMT

Sum ( {<[Cuentas_EERR]= {1} >} [SALDO]) - Before(Sum ( {<[Cuentas_EERR]= {1} >} [SALDO]))

SALDO = Amount of account
Cuentas_EERR = Account Clasification

The difference is correct but the format i dont like it.

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 12:34:38 GMT

I am 100% sure that you need Pick(Dim(... approach to solve this...

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 12:36:50 GMT

Another thread you might be able to check

Pivot table Hide measure column based on dimens... | Qlik Community

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 12:37:08 GMT

And in general the approach is laid out here

Percent of Total in Pivot Table

Re: Sum in a pivot table

sebastian_marti — Fri, 13 Apr 2018 13:48:01 GMT

Could you help me with this script?

I will appreciate this. Please

Re: Sum in a pivot table

sunny_talwar — Fri, 13 Apr 2018 13:54:00 GMT

I want to... but how? you have not shared a sample

Re: Sum in a pivot table

sebastian_marti — Fri, 13 Apr 2018 15:03:57 GMT

Sunny, in the last picture, columns Totales, % Sobre Venta, must be part of Pick(Dim function?

Which values we must specify in the script for Dim? Year dimension? Other value?

We can't understand the reason to use Pick function for this problem.

Pick function choose a value from a list, which values make up the list?

Thanks a lot!