3 Replies Latest reply: Apr 24, 2012 3:10 AM by Ashutosh Paliwal RSS

    Help with Sum function with dataset having duplicate records

      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