Qlik Community

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
nagaiank
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,

Thanks for your reply.

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

Regards,

Jan

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

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

nagaiank
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:

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.