topic line total at header level in QlikView

line total at header level

Thu, 30 Jul 2009 18:00:15 GMT

Hi All,

I'm very new to this and learning as I go along, so please be patient with me [:)]

I have 2 tables, po_headers and po_lines. In po_lines I have line_qty and line_price from which I have created a new field, line_total. What I need is a total of all the lines at header level. I am on Qlikview version 8.2, and I am literally at the beginning of what is going to be a very steep learning curve!

Can anyone point me in the right direction... please

Thank you for reading

line total at header level

Thu, 30 Jul 2009 18:11:40 GMT

Hi P:

All you need to do it write this code in title box of chart properties/general tab:


=sum(total (fieldname) )

Hope this helps..

Thanks

Amit

line total at header level

Thu, 30 Jul 2009 19:22:29 GMT

Hi Amit,

Thank you for your quick reply. I have tried entering what you said (I had already tried this), and it returns the total of all of the po_lines in the table. I guess I want to do a group by po_header_id, but I'm not sure where to do the group by...

I should also maybe mention that I am creating a straight table which can fast change to a pivot table.

To clarify, the query in SQL is:


SELECT h.SEGMENT1 PO_NUM, SUM(l.UNIT_PRICE * l.QUANTITY)
FROM po_headers_all h, po_lines_all l
WHERE h.PO_HEADER_ID = l.PO_HEADER_ID
GROUP BY h.SEGMENT1

Thanks for your time

line total at header level

Thu, 30 Jul 2009 21:26:02 GMT

From the sounds of it, you want something like:

Sum (TOTAL <po_header_id> line_total)

The field in the <> will give the total by that field. In this case, it will give the sum for each po_header_id.

line total at header level

Thu, 06 Aug 2009 22:39:20 GMT

Thanks for your reply, I did try it and found the following error ...

//Error in calculated dimension

I have managed to get around this by adding the following to my load script.

tmpPO_LINES_ALL:
Load PO_HEADER_ID,
 sum(UNIT_PRICE * QUANTITY) as PO_TOTAL
from PO_LINES_ALL.qvd (qvd) group by PO_HEADER_ID;

Thanks for your help guys,