topic Re: Re: Showing Top 5 and Bottom 5 on Bar Chart in QlikView

Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Thu, 15 May 2014 20:33:39 GMT

Hi,

Can anyone please help me with the following? I was trying to reflect, the Top and the Bottom five for the following data's, however QV doesn't show the bottom five's correctly as it has shown on the attachment. The data's are

Name	Value
R	32
D	11
J	9
Y	8
B	8
C	6
X	5
L	4
F	3
N	3
A	2
G	2
K	1
E	0
H	0
I	0
M	0
P	0

The top 5 in Green color are correct which are R - D - J - Y - B, but the bottom 5 in Red color shows only three as A - G - K in stead of showing 5 of them as F - N - A - G - K. Any help please?

Thanks,

Wossen

Re: Showing Top 5 and Bottom 5 on Bar Chart

MK_QSL — Thu, 15 May 2014 20:43:01 GMT

Presentation tab

Untick suppress Zero value

Re: Showing Top 5 and Bottom 5 on Bar Chart

swuehl — Thu, 15 May 2014 20:44:38 GMT

F and N are ranked 9 and 10, but your expressions exclude ranks from 6 to 10, right?

Re: Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Thu, 15 May 2014 20:49:00 GMT

Yes, that is exactly what happened Swuehl!

Re: Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Thu, 15 May 2014 20:50:50 GMT

Hi Manish,

If I do that, I only display the bottom 5 which have zero values, which I don't want that.

Re: Re: Showing Top 5 and Bottom 5 on Bar Chart

swuehl — Thu, 15 May 2014 21:04:14 GMT

One issue is that you need / want to exclude the employees with zero hours from the ranking, right?

So maybe like attached (there is probably an easier solution, it's just too late for me today).

Re: Showing Top 5 and Bottom 5 on Bar Chart

Thu, 15 May 2014 21:05:20 GMT

try this expression

If (Rank(Sum([Utilized hours])) >= (count(total DISTINCT Employee)-5) or Rank(Sum([Utilized hours])) <=5, Sum([Utilized hours])+1)

i added one to your sum since it doesn't display Zeros in bar chart.

Re: Showing Top 5 and Bottom 5 on Bar Chart

anlonghi2 — Thu, 15 May 2014 22:54:06 GMT

Hi Wosse, try with this:

if(num(Rank(Sum([Utilized hours]),3))<=5 or num(Rank(Sum([Utilized hours]),3))>= 10, Sum([Utilized hours]),null())

you have to check "Suppress zero value" from Presentation tab.

Best regards Andrea

Re: Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Fri, 16 May 2014 13:26:45 GMT

Thanks Andrea and you nailed it! Have a wonderful weekend!

Wossen

Re: Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Fri, 16 May 2014 13:29:30 GMT

Thanks v4Shankar for your effort, but it still shows me the zero's value at the bar chart. Andrea and Swuel come up with the correct expression formula that give me exactly what I want!

Regards,

Wossen

Re: Showing Top 5 and Bottom 5 on Bar Chart

wossenhh — Fri, 16 May 2014 13:29:57 GMT

Well done Swuehl! Thanks so much!

Wossen