3 Replies Latest reply: Jun 6, 2012 9:37 AM by Yaniv Egozi

# Sum total in load - problem

G'day,
I am fairly new to QV
I have sales table
 Invoice # Prod Product_Line  (PL) Sales 1 A 10 100 1 B 11 150 1 C 17 -50 1 Total 200 2 D 10 100 2 E 12 200 2 Total 300 3 D 10 100 3 F 11 150 3 G 11 200 3 Z 17 -50 3 Total 400
I want to add 2 fields that calculate the total  of product line and the total inv without the product line whice will look like this.
 Invoice # Prod Product_Line  (PL) Sales Total  For PL 17 in Inv Total  Inv Without PL = 17 1 A 10 100 -50 250 1 B 11 150 -50 250 1 C 17 -50 -50 250 1 Total 200 2 D 10 100 0 300 2 E 12 200 0 300 2 Total 300 3 D 10 100 -150 450 3 F 11 150 -150 450 3 G 11 200 -150 450 3 W 17 -100 -150 450 3 Z 17 -50 -150 450 3 Total 300
I have tried to use sum(total<Invoice_no>(if(product_line <> 17,sales)))
but found that I cannot use sum total in the load script.
any help will be appreciated
Yaniv
• ###### Sum total in load - problem

Yaniv

In the load script, you would use group by to get the same effect,

LOAD Sum(If (product_line <> 17, Sales)) As Sales

...

From Sales.xlsx

Group By Invoice_no

As a general suggestion, are you sure that you want to filter the values in the load script? Usually I would not filter the incoming data like this and rather filter the expressions in the chart or table.

Hope that helps

Jonathan

• ###### Re: Sum total in load - problem

I agree with Jonathan that it seems better to do your calculation in the chart object, to make sure that the result corresponds to the current selections you have made in the application. For example if you would has exclided the W product the static sum from load script could be misleading in a chart object. See attached example, where you also are able to alter the prodline to sum over dynamically.

• ###### Sum total in load - problem

Jonatan Thanks

I know that it's best to to filter it in the chart which is what I did at the begining, but my problem was that had to leave the product_line as a dimention in the chart as when I took it off the result changed.

Loading it in the script gave me the option to calculate the new_sales  per each produc

Toni I have tried your suggestion and my problem with it is if I select PROD A for example and PLPickList 17 it doesn't give me the -50.