Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chinnukool
Contributor III
Contributor III

Need help calculating PERCENTILES...

Hi Experts,

I have a dashboard where active user counts per line of business(LOB) are measured by user activity.

Now the new requirement is to create profiles based on the user activity percentile.


power - if activity is greater than 75 percentile

regular - if activity is greater than 25 percentile and less than 75 percentile

casual - if activity is greater than 0 percentile and less than 25 percentile

inactive - if there is no activity

These profiles has to be displayed in a pie chart.


And ones these profiles are created, i should be able to use these in calculating  adoption percentages

logic: (power+regular)/all users

Also i should be able to apply filters on these percentiles - like YEAR, MONTH.

Can someone please help me achieve this?

Any help is much appreciated. Thanks!

7 Replies
emeehleder
Contributor
Contributor

You can try to create variables for your different fractiles using an equation like this...

=fractile(((Power+Regular)/AllUsers),.75)  

=fractile(((Power+Regular)/AllUsers),.25)  

Then in your expression try something like if(((Power+Regular)/AllUsers)>=vFractile75,'Power')

sunny_talwar

Can someone please help me achieve this?

Sure, but where is the data? what have you already tried? from what you tried.... what all things were not working

chinnukool
Contributor III
Contributor III
Author

Sorry Sunny for not providing all the details.

I've created a resident table to calculate the counts of user activitiesScreen Shot 2018-06-14 at 12.58.39 PM.png

And added this new table in the data model by USER ID

Screen Shot 2018-06-14 at 12.58.51 PM.png

Now i'm trying to built the below chart as per the requirement.

Screen Shot 2018-06-14 at 12.59.32 PM.png

The first part, count(USER) by USER_LOB is done.

I'm stuck at creating the profiles and using it in the expression to add next to the user count as adoption%.

Screen Shot 2018-06-14 at 1.04.48 PM.png

Please let me know if any additional information is required. Thanks!

shiveshsingh
Master
Master

Can you share the excel?

chinnukool
Contributor III
Contributor III
Author

Hi Shivesh,

Please see below:

I need to calculate the percentile for the whole data set and i should be able apply filters on that. Thanks!

Screen Shot 2018-06-14 at 4.22.41 PM.png

chinnukool
Contributor III
Contributor III
Author

Hi Erica,

I do not have the power , regular and other user profiles created right now.

can you please guide on how to create those profiles so i can try creating these variables using them? Thanks!

chinnukool
Contributor III
Contributor III
Author

HI,

I created 2 Variables to calculate percentile

vPower = Fractile(user_profile,0.75)

vRegular = Fractile(user_profile,0.25)

and to calculate the adoption % I tried

if(USER_Profile >= vRegular, count(total(aggr(if(USER_Profile >= vRegular, count(user_profile)),user_profile),0)

/count({<IS_Legal = 1>} total user)

Issue I see here is when I don’t add USER_profile as a dimension,it is displaying 0 in the chart.

can someone please help me have the adoption percentage displayed without the USER_profile column added in the chart.

thanks!