20 Replies Latest reply: Apr 13, 2018 11:13 AM by Sunny Talwar

# Sum in a pivot table

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

DescriptionTotals% Sales
Sales1000100%
Cost50050%
Other Cost20020%

Sebastian

• ###### Re: Sum in a pivot table

May be this

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

• ###### Re: Sum in a pivot table

Wonderful!

It's works!

Thank you

• ###### Re: Sum in a pivot table

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:

Best regards,

Sebastián Martínez

• ###### Re: Sum in a pivot table

Then try this

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

• ###### Re: Sum in a pivot table

It works!!!

Thanks a lot!

• ###### Re: Sum in a pivot table

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)

• ###### Re: Sum in a pivot table

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

• ###### Re: Sum in a pivot table

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

• ###### Re: Sum in a pivot table

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

Could you help me with this script?

• ###### Re: Sum in a pivot table

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

• ###### Re: Sum in a pivot table

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!

• ###### Re: Sum in a pivot table

Pick Dim method is sort of a hack that you need to use because QlikView doesn't have a direct way of dealing with this kind of problem. The goal is to have multiple dimensions with just a single expression. In your case, this will be the values for Dim

and Year will have a condition for the last column.

Does that make sense?