QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Sum with where statement

Hello Qlikview experts,

I am a newbee on Qlikview.

I use Qlikview 7.5.

I wan to create a sales report.

Need a formula to calculate new field “Sum” wich is the sum of field Quantity where the fields “Cust”, “Item Numb”, “Customer Item and “”Month” are same.

 Cust Item Numb Customer Item Month Quantity Date Sum 100376 30308A00Z 1254489 1 36 8-01-13 36 100003 30309B00Z 1688560 1 24 2-01-13 24 100003 30309B00Z 1688560 2 24 8-02-13 24 100003 30327A00Z 1366897 1 12 3-01-13 24 100003 30327A00Z 1366897 1 12 7-01-13 24 100003 30327A00Z 1366897 2 12 8-02-13 12 100003 30329A00Z 1369921 1 30 7-01-13 30 100003 30345A00Z 1425520 1 32 4-01-13 32 100003 30369A00Z 1450325 1 16 3-01-13 16 100003 30369A00Z 1450325 2 16 7-02-13 16 100447 30309B00Z A9602641901 1 2 2-01-13 2 100447 30309B00Z A9602641901 2 30 8-02-13 30 100447 30327A00Z A9602641901 1 16 3-01-13 16 100447 30327A00Z A9602641901 1 16 7-01-13 32 100447 30327A00Z A9602642001 2 2 8-02-13 2 100447 30329A00Z A9602642001 1 30 7-01-13 30

Any help will be apreciated,

Regards,

Jan G.

5 Replies
Valued Contributor III

Re: Sum with where statement

If you want to calculate the sum in script, the following may help you:

If the detailed data table name is Details

Summary:

NoConcatenate LOAD Cust, [Item Numb], [Customer Item], Month, Sum(Quantity) as SumQty

resident Details Group By Cust, [Item Numb], [Customer Item], Month;

If you want to display the sum on a chart, create a pivot table chart with Cust, [Item Numb], [Customer Item], Month as dimensions and Sum(Quantity) as expresion.

Hope this helps.

Not applicable

Re: Sum with where statement

Hello krishnamoorthy,

As i am running Qlikview 7.5 function Concatenate doesn't work. Is there an other solution?

Regards,

Jan

Valued Contributor III

Re: Sum with where statement

Did you try with 'NoConcatenate' prefix?

Highlighted
Not applicable

Re: Sum with where statement

Krishna,

Here the script.

[Item Number],

[Customer Item],

Quantity,

Date,

Month,

FROM [..\..\..\xxx\omzet\SO 2013-01-08.xls] (biff, embedded labels, table is [Blad2\$]);

How should it look with Concatenate expression?

Thanks,

Jan

Valued Contributor III

Re: Sum with where statement

Try the following. You may have synthetic key, but I think it will not affecf the result.

Table1:

[Item Number],

[Customer Item],

Quantity,

Date,

Month,

FROM [..\..\..\xxx\omzet\SO 2013-01-08.xls] (biff, embeddedlabels, table is [Blad2\$]);

Table2:

[Item Number],

[Customer Item],

Month,

Sum(Quantity) as SumQty

Resident Table1

Group By Cust, [Item Number], [Customer Item], Month;

I do not have access to Qlikview version 7.5 in order to test the above script.

Hope this helps.