![leale1997 leale1997](https://community.qlik.com/legacyfs/online/avatars/a1006_5.png)
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum by Group
I am trying to sum a data by group so that it doesn't count duplicate records.
In the above data sample I would like it to sum the values based on both the "Description" field and the "ID" field. So it will count multiple values when the ID is the same but the Description is different.
Accepted Solutions
![dinuwanbr dinuwanbr](https://community.qlik.com/legacyfs/online/avatars/a122658_10632772_10202171473749004_969720238620191217_n.png)
![Creator III](/html/@6B5206F7B2906F7249673391408B4801/rank_icons/Community_Gamification-Ranking-Icons_16x16-Creator.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Erika,
Try this
LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description, Batch, Delivery, Material, SU, "Qty (stckpg unit)", BUn,"Delivery date",if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')),If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10;
you need to use all the fields except aggregation fields (like sum, avg, max, etc) in your group by clause
rgds,
Tharindu
![effinty2112 effinty2112](https://community.qlik.com/legacyfs/online/avatars/a199325_signals_cr.png)
![Master](/html/@6539E2A21CD38F4D204A51BD91D7701C/rank_icons/Community_Gamification-Ranking-Icons_16x16-Master.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Erika,
A straight table with dimensions Description and ID with expression Sum(Qty) will give you what you need. If you want a script solution then something like:
LOAD
Description,
ID,
Sum(Qty) as Qty
From .....
Group by Description,ID;
should work.
Regards
Andrew
![leale1997 leale1997](https://community.qlik.com/legacyfs/online/avatars/a1006_5.png)
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So I am trying to use the script below.... Not working.... any ideas?
LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description,Batch;
SQL SELECT *
FROM "DEV_SI_Data".dbo."SAP_Distribution_4" WHERE Year("Delivery date")> 2013 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not working means throwing any error?
![leale1997 leale1997](https://community.qlik.com/legacyfs/online/avatars/a1006_5.png)
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
When I use the code with no 'sum' fields:
LOAD Delivery,
Material,
Description,
Batch as PCMBatch,
"Delivery quantity" as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as Format_DD,
Date# ("Delivery date") as ConvertDateNumber,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
"Qty (stckpg unit)")) as Qty2
Group by Description,Batch;
When I put in the 'sum' code the table wont connect.
LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description,Batch;
SQL SELECT *
FROM "DEV_SI_Data".dbo."SAP_Distribution_4" WHERE Year("Delivery date")> 2013 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You need to aggregate using all non aggregate fields with in Group By..
![dinuwanbr dinuwanbr](https://community.qlik.com/legacyfs/online/avatars/a122658_10632772_10202171473749004_969720238620191217_n.png)
![Creator III](/html/@6B5206F7B2906F7249673391408B4801/rank_icons/Community_Gamification-Ranking-Icons_16x16-Creator.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Erika,
Try this
LOAD
Delivery,
Material,
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
SU,
"Qty (stckpg unit)",
BUn,
"Delivery date",
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Group by Description, Batch, Delivery, Material, SU, "Qty (stckpg unit)", BUn,"Delivery date",if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')),If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10;
you need to use all the fields except aggregation fields (like sum, avg, max, etc) in your group by clause
rgds,
Tharindu
![andrei_delta andrei_delta](https://community.qlik.com/legacyfs/online/avatars/a296038_thumb-1920-864910.png)
![Partner - Creator III](/html/@D9016B49FD846E1AD88CBE9C67131CF6/rank_icons/Community_Gamification-Ranking-Icons_16x16-Partner.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hello,
it's not working because the group by clause accepts only aggregation like the others said (max, sum, min, avg etc.), another issue is with the fields that you are making the grouping for instance:
you made the group by like this: "Group by Description,Batch;"
so the script should be looking like this:
LOAD
Description,
Batch as PCMBatch,
Sum("Delivery quantity") as PCMDeliveryQty,
if(mid([Delivery date],4,2)<=12, Date(Date#([Delivery date],'DD/MM/YYYY'),'MM/DD/YYYY'),Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY')) as New_DatePCM,
If ("Description" like '*Flu*',"Qty (stckpg unit)" *10,
if("Description" like '*Q/LAIV*',"Qty (stckpg unit)" *10,
Sum("Qty (stckpg unit)"))) as Qty2
Resident X*
Group by Description,Batch;
X* being the table you are loading data from.
And i don;t know if you want to make the new field based on the group by you are using, if not it shouldn't be there at all.
Hope it helps
Andrei
![](/skins/images/0D8EDF9BC3A79D4FAB93CFEB4AC20BF6/responsive_peak/images/icon_anonymous_message.png)