topic Re: Sum by Group in QlikView

Sum by Group

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sum by Group

effinty2112 — Tue, 19 Sep 2017 16:14:57 GMT

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

Re: Sum by Group

leale1997 — Tue, 19 Sep 2017 21:16:42 GMT

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 ;

Re: Sum by Group

Anil_Babu_Samineni — Wed, 20 Sep 2017 02:56:04 GMT

Not working means throwing any error?

Re: Sum by Group

leale1997 — Wed, 20 Sep 2017 03:08:25 GMT

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.

Re: Sum by Group

Anil_Babu_Samineni — Wed, 20 Sep 2017 03:24:56 GMT

You need to aggregate using all non aggregate fields with in Group By..

Re: Sum by Group

dinuwanbr — Wed, 20 Sep 2017 03:50:43 GMT

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

Re: Sum by Group

andrei_delta — Wed, 20 Sep 2017 07:17:51 GMT

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