topic Re: Total Sum Expression in QlikView

Total Sum Expression

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

I am having a weird problem with the sum of many freight charges where the total sum of DEL_GBP_FREIGHT should be 20.00 as there is only 1 lot of 20.00 in the set of data, but the expression is calculation 40 and I have no idea why!!

This might be something silly but I really can't figure it out. I think its having an effect on another expression too.

Any ideas?

Re: Total Sum Expression

Anonymous — Thu, 18 May 2017 10:31:00 GMT

Upload sample data am using personal edition. I can't open the application.

Re: Total Sum Expression

Anil_Babu_Samineni — Thu, 18 May 2017 10:45:28 GMT

Try to remove filter for Dealer Name and then look whether values are showing which is hide the 20 ??

Re: Total Sum Expression

antoniotiman — Thu, 18 May 2017 10:48:17 GMT

Hi,

You have 2 rows

Regards,

Antonio

Re: Total Sum Expression

Frank_Hartmann — Thu, 18 May 2017 10:54:35 GMT

may be try:

sum( DISTINCT DEL_GBP_FREIGHT)

hope this helps

Re: Total Sum Expression

khaycock — Thu, 18 May 2017 11:21:44 GMT

But when I want to do a total sum in another chart of all the £20 charges, I can't DISTINCT it because it will return only 20 when it should actually be 60?

If i take out the DISTINCT I get the problem again...

What could I do in this situation?

I'm also trying to get a sum of the Split UPS Charge but that seems impossible too

Re: Total Sum Expression

florentina_doga — Thu, 18 May 2017 12:16:07 GMT

you have 2 rows for order no.100262879

see attach

Re: Total Sum Expression

florentina_doga — Thu, 18 May 2017 12:23:51 GMT

lastscandatetime and consignment have different value

Re: Total Sum Expression

khaycock — Thu, 18 May 2017 13:42:19 GMT

Even if remove those rows from the script it is still saying 40..

Re: Total Sum Expression

florentina_doga — Thu, 18 May 2017 13:46:14 GMT

maiby

ItemDetail:

LOAD distinct ManifestDate,

// Consignment,

// AddressCode,

// Service,

// CustomerCode,

Name,

Address1,

Address2,

Address3,

Address4,

PostCode,

Country

// Package,

// Reference,

// From_Name,

// Route,

// Weight,

// LastScan,

// LastScanDatetime,

// Reference2,

// Reference3,

// Reference4

FROM

(biff, embedded labels, table is Items$);

Re: Total Sum Expression

florentina_doga — Thu, 18 May 2017 13:47:34 GMT

Commenting on fields does not remove duplicates

Re: Total Sum Expression

khaycock — Thu, 18 May 2017 13:58:04 GMT

Ahhhh that's amazing thank you!!

Do you think you could help me with getting a total of the split charges as well? I'm basically trying to SUM the total UPS charge which is £19.64 per day of deliveries. I have the split sum working in the pivot table fine but now i need to sum those 19.64s to create a variance against the other freight charge for the Total Freight Charge table.

I've attached another qvw with the expression in it...