topic Re: Sum with where statement in QlikView

Sum with where statement

Sat, 12 Jan 2013 12:59:38 GMT

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.

Re: Sum with where statement

nagaiank — Sat, 12 Jan 2013 13:47:28 GMT

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.

Re: Sum with where statement

Sat, 12 Jan 2013 16:47:30 GMT

Hello krishnamoorthy,

Thanks for your reply.

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

Regards,

Jan

Re: Sum with where statement

nagaiank — Sat, 12 Jan 2013 18:05:40 GMT

Did you try with 'NoConcatenate' prefix?

Re: Sum with where statement

Sat, 12 Jan 2013 18:48:11 GMT

Krishna,

Here the script.

Load Cust,

[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

Re: Sum with where statement

nagaiank — Sat, 12 Jan 2013 19:18:06 GMT

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

Table1:

Load Cust,

[Item Number],

[Customer Item],

Quantity,

Date,

Month,

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

Table2:

Load Cust,

[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.