Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
farheenayesha
Creator
Creator

How to create buckets using percentage value

Hi,

Please help me to create below buckets using % to Goal values. And also how to calculate count of Sales Rep for each bucket.

% to Goal - Sum(Sales)/Sum(PY Sales)

Buckets:

<=95% -if  % to Goal <=0.95

95%-97% - if % to Goal >0.95 and <=0.97

98%-99% - if % to Goal >0.97 and <=0.99

>100%% to Goal >=1

Using the above bucket i want to calculate count of Sales rep for each bucket as below

Count of Sales Rep:

<=95% - 18

95%-97% - 5

98%-99% - 5

>100% - 37


Please help me to create a bar chart with above buckets and count of sales rep as below. In the below bar graph instead of 65,65,52,51 i want to get values as 18,5,5, 37

chart 2.JPG

1 Solution

Accepted Solutions
sunny_talwar

I got close to what you need, but not exact

Capture.PNG

View solution in original post

9 Replies
ashok1203
Creator II
Creator II

Where you need to creates this buckets it's in script or front end chart.

AAK
sunny_talwar

I got close to what you need, but not exact

Capture.PNG

effinty2112
Master
Master

Hi Ayessha,

I changed your bucket definition a little because there was no bucket for >99%<100%.

Got this chart:

1.png

The Bucket dimension is:

=Aggr(

Pick(

Match(-1,

Sum (Sales)/Sum (Goal)<=0.95,

Sum (Sales)/Sum (Goal)<=0.97,

Sum (Sales)/Sum (Goal)<=1,

-1),

'<=95%',

'<=97%',

'<=100%',

'>100%')

,[Sales Representative Name])

Regards

Andrew

PS

Sort by expression Sum (Sales)/Sum (Goal)

farheenayesha
Creator
Creator
Author

Thank you so much sunny. It is exactly what i wanted.

farheenayesha
Creator
Creator
Author

Thank you so much Andrew. Even your answer is correct. Unfortunately i cannot mark it as correct. Hence marked it as helpful.

Shaunak3
Contributor
Contributor

Hi Sunny,

Can you please post the Solution in text file as i am using Qlik Sense and i do not have access to Qlik View for viewing file. Any help would be appreciated. Thanks,

 

Regards,

Shaunak

Shaunak3
Contributor
Contributor

Hi Andrew,

Can you please explain this snippet, especially what does the -1 do in the above script?

 

Regards,

Shaunak

effinty2112
Master
Master

Hi, Sorry for the late reply.  -1 means TRUE.

Suppose Sum (Sales)/Sum (Goal) = 0.96, so "Sum (Sales)/Sum (Goal)<=0.97" is the first true statement inside MATCH.

Match(-1,

Sum (Sales)/Sum (Goal)<=0.95,

Sum (Sales)/Sum (Goal)<=0.97,

Sum (Sales)/Sum (Goal)<=1,

-1)

Evaluating "Sum (Sales)/Sum (Goal)<=0.95" will return 0. Because in this case that statement is not true. 0 does not match -1 so the MATCH statement moves on to the next line. The next line is true, will return -1 and will match the first argument of the MATCH statement so the MATCH statement will return 2.

The final -1 is there to catch any examples where Sum (Sales)/Sum (Goal)>1 because if Sum (Sales)/Sum (Goal)>1 the MATCH statement will MATCH -1 to -1 and return 4.

Kind regards

 

Andrew

Shaunak3
Contributor
Contributor

Hi Andrew,

Thanks for your reply and really appreciate your efforts for explaining the snippet to me,it was helpful to me in a live scenario. Thanks a lot!!

 

Regards,

Shaunak