topic How to create buckets using percentage value in QlikView

How to create buckets using percentage value

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

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

Re: How to create buckets using percentage value

ashok1203 — Tue, 19 Sep 2017 12:17:02 GMT

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

Re: How to create buckets using percentage value

sunny_talwar — Tue, 19 Sep 2017 12:22:44 GMT

I got close to what you need, but not exact

Re: How to create buckets using percentage value

effinty2112 — Tue, 19 Sep 2017 13:11:57 GMT

Hi Ayessha,

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

Got this chart:

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

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

Re: How to create buckets using percentage value

farheenayesha — Tue, 19 Sep 2017 14:38:58 GMT

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

Re: How to create buckets using percentage value

farheenayesha — Tue, 19 Sep 2017 14:41:13 GMT

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

Re: How to create buckets using percentage value

Shaunak3 — Tue, 07 Jan 2020 06:30:18 GMT

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

Re: How to create buckets using percentage value

Shaunak3 — Tue, 07 Jan 2020 06:56:06 GMT

Hi Andrew,

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

Regards,

Shaunak

Re: How to create buckets using percentage value

effinty2112 — Mon, 20 Jan 2020 09:28:41 GMT

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

Re: How to create buckets using percentage value

Shaunak3 — Fri, 24 Jan 2020 09:52:01 GMT

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