Grouping and Sorting data to understand insights

[#1]

I have approx. 45,000 records of  customer rewards points. Total USER_ID's are 15,000. This rewards are collected over period of 5 months.

The Table as follows

USER_ID,

YYYYMM,

TOTAL_POINTS,

TOTAL_ONLINE_POINTS,

TOTAL_POS_POINTS,

PT_POS_FOOD,

PT_POS_TRAVEL,

PT_POS_GROCERIES,

PT_POS_ENTERTAINMENT,

PT_POS_CLOTHS,

PT_POS_MEDICAL,

PT_ONLINE_FOOD,

PT_ONLINE_TRAVEL,

PT_ONLINE_GROCERIES,

PT_ONLINE_ENTERTAINMENT,

PT_ONLINE_CLOTHS,

PT_ONLINE_MEDICAL

Now I want to understand insights of data as follows

Sort the TOTAL_POINTS, 0 to MAX(TOTAL_POINTS)

I want to group  Based on 1500 USERS , i.e First 1500 users(0 to 1500 th USER's  TOTAL_POINT in one group, 1501th TOTAL POINT of USER  to 3000th TOTAL POINT of USER falls in another group etc..)  and Analyze these groups based on ONLINE, POS - POINT OF SALE, ONLINE_TRAVEL, ONLINE_GROCERIES, OFFLINE_... etc etc. Some users may have got rewards from multiple transactions example  PT_POS_FOOD and PT_ONLINE_TRAVEL

What is the best way to implement and visualizing data.

Thank you

[jjordaan]

Hi Sairam06,

You can use the class function as a calculated dimension.

This is the QlikView help explanation:

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

As an attachment you will find an example

[#3]

Hi Jeroen Jordaan

Thank you for the quick reply. I am new to qlikview and we have raised a purchase request for a licence. So At  present I am using Personal edition and unable to open the attachment.

Neverthless Let me clarify the doubt.

Suppose my Sorted  TOTAL_POINTS, as follows

LOWEST TOTAL POINT :   0

SECOND TOTAL POINT :   0

THIRD TOTAL POINT:         1

:

:

:

1500'th TOTAL POINT     :   52

1501 TOTAL POINT         : 52

:

:

:

3000 TOTAL POINT        ,100

:

:

:

4500 TOTAL POINT  :132

4501 TOTAL POINT :133

:

:

:6000 TOTAL POINT :142

6001 TOTAL POINT : 142

.:

:

:

15000 TOTAL POINT: 18000

I need to find 1500th,3000th,4500'th...TOTAL_VALUE.

How do I find?

[jjordaan]

Sairam06,

The values that you want to find are they always the same values?
And do you only want to display the values in your example (1500th,3000th,4500'th)

[#5]

Jeroen Jordaan

The values 1500th,3000th,.... are not fixed. We take the total number of users and analyze every 10% of users in a group. In my case for a particular month I am analyzing we have 15,000 USERS who scored reward points.

In some month the user count may increase/decrease to another number.

So this I arrive using a variable vMemGroup=Round( (COUNT(DISTINCT if( TOTAL_POINTS >0,USER_ID))/10))