Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
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,
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())
Use column(3) as your expression and put a checkmark in "relative".
That opinion is not available for pivot, is it?
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
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.
hi Pascal - did you come right with this?I have the same error in this link: http://community.qlik.com/thread/51448