topic Help with Sum function with dataset having duplicate records in QlikView

Help with Sum function with dataset having duplicate records

Tue, 24 Apr 2012 03:26:53 GMT

Hi,

I am new to QlikView and am struggling with using sum function with dataset having duplicate records.

I have a dataset which has duplicate records based on a 'bookingid', where it gets split by months 'Jan', 'Feb', 'Mar', etc.. into different records but Quantity field will have the same value, whereas the cost gets split proportinately.

I would like to only get the sum of quantity based on a distinct 'bookingid'. For example, refer the below dataset

BookingID Month Quantity Cost

001 Jan 8 3100.00

001 Feb 8 2900.00

001 Mar 8 3100.00

I tried to use Sum({<DISTINCT BookingID>} Quantity), QV sums it up as 24, my understanding was by providing the set analysis parameters to only consider DISTINCT BookingID's, the expected result should be 8. Am I doing anything wrong here?

I am using this in a chart with expression that summarises the Quantity for distinct BookingID's against a few dimensions such as 'Cost Type', 'Vendor', etc..

Much appreciate any help to get this right.

Cheers

Sharad

Help with Sum function with dataset having duplicate records

Tue, 24 Apr 2012 06:31:25 GMT

Hi,

While there are other ways also to do it. One very simple way is to use Quantity as a dimension rather than Expression because in your scenario it is working as a dimension only.

Or if you want to put it into expressions then you can simply use avg function also rather then using sum, sum here does not make sense because you don't want to sum the quantities.

So, just use avg(Quantity) then you will see correct data only.

Ashutosh

Help with Sum function with dataset having duplicate records

Tue, 24 Apr 2012 07:03:22 GMT

Thanks Ashutosh, it worked like a breeze, it just takes a while to understand how QV set analysis functions work and you are correct I was using the sum incorrectly in this context!

I had multiple sets of such data with various conditional computations on the Quantity required and displayed in

the charts, hence I had used expressions, here is the formula I managed to use to get the average of quantity aggregated by bookingid and did the sum - sum(aggr(avg(Quantity),BookingID))

All my Quantity based computations are working fine now.

Thanks again for your help!!

Re: Help with Sum function with dataset having duplicate records

Tue, 24 Apr 2012 07:10:33 GMT

Thanks!

Ashutosh