6 Replies Latest reply: Jun 10, 2011 7:21 AM by Mani Kolli RSS

    Help Needed....Cumulative sum and cumulative count of record

    Mani Kolli

      This is my table having three columns.

      SiteID      UserID      Sales

      1           A1               100
      1           A2               800
      1           A3                 50
      1           A4               150
      2           B1                 70
      2           B2                 50
      2           B3               900
      2           B4               200
      2           B5                 30
      2           B6                 40
      2           B7                 60
      2           B8                 10
      2           B9                   5


      The requirement is Business wants to know the count of users who are generating 95% of total sales in a Site.
      So i can first sort the users based on Sales.

      SiteID      UserID      Sales

      1                A2           800
      1                A4           150
      1                A1           100
      1                A3             50

      2                B3           900
      2                B4           200
      2                B1             70
      2                B6             60
      2                B2             50
      2                B6             40
      2                B5             30
      2                B8             10
      2                B9               5

       

      Then do the cumilative sum of sales and select the count of users who generates 95% of Sales.

      SiteID UserID Sales      CumSum
      1           A2      800        800
      1           A4      150        950
      1           A1      100      1050
      1           A3       50       1100

      2           B3      900       900
      2           B4      200      1100
      2           B1       70       1170
      2           B6       60       1230
      2           B2       50       1280
      2           B6       40       1320
      2           B5       30       1350
      2           B8       10       1360
      2           B9        5        1365

       

      so, 95% of Sales for SiteID 1 = 1045 and count of users who generate it are 3
      so, 95% or Sales for SiteID 2 = 1297 and count of users who generate it are 5.

       

      Can some body help me in achieving this in QlikView? I tried Rangesum and RangeCount functions but i am unable to do this as i am new to qlikview.

       

      Or if there is any other way of acheving this requirement, kindly let me know how to do so.