Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

egoziyan
New Contributor II

Sum total in load - problem

G'day,
I am fairly new to QV
I have sales table
Invoice #ProdProduct_Line  (PL)Sales
1A10100
1B11150
1C17-50
1Total200
2D10100
2E12200
2Total300
3D10100
3F11150
3G11200
3Z17-50
3Total400
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 #ProdProduct_Line  (PL)SalesTotal  For PL 17 in InvTotal  Inv Without PL = 17
1A10100-50250
1B11150-50250
1C17-50-50250
1Total200
2D101000300
2E122000300
2Total300
3D10100-150450
3F11150-150450
3G11200-150450
3W17-100-150450
3Z17-50-150450
3Total300
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
1 Solution

Accepted Solutions
MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Employee
Employee

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.

egoziyan
New Contributor II

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.