topic Re: Need help calculating PERCENTILES... in QlikView

Need help calculating PERCENTILES...

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Need help calculating PERCENTILES...

emeehleder — Thu, 14 Jun 2018 16:38:17 GMT

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')

Re: Need help calculating PERCENTILES...

sunny_talwar — Thu, 14 Jun 2018 16:40:52 GMT

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

Re: Need help calculating PERCENTILES...

Anonymous — Thu, 14 Jun 2018 17:07:17 GMT

Sorry Sunny for not providing all the details.

I've created a resident table to calculate the counts of user activities

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

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

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%.

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

Re: Need help calculating PERCENTILES...

shiveshsingh — Thu, 14 Jun 2018 18:38:56 GMT

Can you share the excel?

Re: Need help calculating PERCENTILES...

Anonymous — Thu, 14 Jun 2018 20:25:04 GMT

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!

Re: Need help calculating PERCENTILES...

Anonymous — Thu, 14 Jun 2018 20:26:51 GMT

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!

Re: Need help calculating PERCENTILES...

Anonymous — Fri, 15 Jun 2018 15:21:12 GMT

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!