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

[#1]

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.

[#2]

And adding to the above, my final report should display like this

SiteID    95% of Sales   UsersCount

1            1045               3

2            1297               5

[#3]

Hello,

try this:

load SiteID,

       count(UserID) as UserCount,

       sum(Sales*0.95) as [95% of Sales]

From ....

group by SiteID

;

[#4]

user count is not right. Try if statement to archive users.

[#5]

UserCount will not be right. It just gives the usercount of table. But i want user count who contribute 95% of sales.

[pat_agen · Accepted solution]

hi,

couldn't work out how to do it 100% dynamically so this solution mixes some script with some dynamics - ie you can use a slider to define what % of sales you are interested in.

[#7]

Thanks a lot Pat. Awesome solution ....very nice one