Sum total in load - problem

[egoziyan]

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

[jonathandienst · Accepted solution]

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

[ToniKautto]

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.

[egoziyan]

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.