Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Did you try with 'NoConcatenate' prefix?
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
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.