Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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.

1 Solution

Accepted Solutions
pat_agen
Valued Contributor

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

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.

6 Replies
Not applicable

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

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

SiteID    95% of Sales   UsersCount

1            1045               3

2            1297               5

Not applicable

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

Hello,

try this:

load SiteID,

       count(UserID) as UserCount,

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

From ....

group by SiteID

;

Not applicable

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

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

Not applicable

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

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

pat_agen
Valued Contributor

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

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.

Not applicable

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

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

Community Browser