Sum/Total of column

[#1]

Hi,

how can i get the total of a column based on other columns?

I´ve got a pivot-table with followig expressions:

Product | Amount | Price | Turnover | % Turnover

1| sum(Amount) | 9,99 | column(1)*column(2) | should be column(3) / total of column(3)

It seems to be a small matter, but i can´t find a solution for getting the total of column(3). If i use sum(amount)*Price it´s not the right value for total.

Thanks.

[#2 · Accepted solution]

Hi

I think I would do two things here, if you try to use a Total expression like Sum(Total Amount) * Price then it's not going to work as you'd expect, so I would do the following:

In your load script, calculate Amount * Price to give a field such as OrderValue.

In you chart, for column 5 it will be Sum(OrderValue) / Sum( Total OrderValue)

Putting the Total keyword in will force it to use the total of the column.

Regards,

[#3]

Thanks for your advice.

The problem is, that the values aren´t in the same table. So i have to join several tables for calculating the OrderValue.

It would be nice if there is an opprtunity for fetching the total of a column directly like (total column())

[johnw]

Use column(3) as your expression and put a checkmark in "relative".

[#5]

That opinion is not available for pivot, is it?

[#6]

I'd suggest computing the sum in the loadscript already:

Total:
LOAD sum( OrderValue ) AS Total RESIDENT Orders;
LET vTotal = FieldValue( 'Total', 1 );

then you can use $(vTotal) in your chart, would that help?

Hellmar

[#7]

Hi Hellmar,

yes, that would help.

Calculating my ordervalue isn´t as easy as described in your code, but doing it in script is the only solution for it.

Thanks and regards.

[#8]

hi Pascal - did you come right with this?I have  the same error in this link: http://community.qlik.com/thread/51448