topic Re: Set Analysts Exclude problem in QlikView

Set Analysts Exclude problem

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

Hello,

I want to exclude two values, Unfortunately, The pop up coming only exclude values.

Help me Please

=If((Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score])) >=70,RGB(255,0,0),

if((Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score])) <70 and (Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score])) >50 ,RGB(0,255,0),

if((Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score])) <=50 ,RGB(255, 165, 0)

)

Re: Set Analysts Exclude problem

Anonymous — Mon, 04 Jul 2016 16:03:59 GMT

Hi Anil

Are you taking about the pop up values displayed in a chart? If so what version of Qlikview are you using? You can suppress the pop-up values in the charts presentation tab and then create an additional expression that you can display as a custom pop-up. Can you explain a little more what your requirement is please?

Kindest Regards

Brian

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Mon, 04 Jul 2016 16:06:15 GMT

Brian,

I am working on Funnel Chart.

Re: Set Analysts Exclude problem

Anonymous — Mon, 04 Jul 2016 16:15:41 GMT

Anil

The chart type shouldn't matter. Do you have an option to un-tick the pop-up labels on the chart's properties presentation tab? If you un-tick that the automatic pop-ups will disappear and then you can create your own expression to display exactly what you want to show. However the reason for asking which version you have is there was an error in Qlikview 11 sr6 onwards I believe which meant if you un-ticked the pop-up on the presentation tab it would also hide your custom pop-up. However this bug was fixed in sr10. Hope this link may be useful for you.

Re: Custom popup only appear if Popup labels checked

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Mon, 04 Jul 2016 16:17:44 GMT

Brian,

I am not asking about Pop-Up.

I have one field, In that i want to exclude two values. I am asking about Set Analysis. I wrote some code on that, I am asking you is that is correct ot Not?

- Anil

Re: Set Analysts Exclude problem

Anonymous — Tue, 05 Jul 2016 06:27:36 GMT

Hi Anil,

Which value are you trying to exclude? Otherwise, have you tried using =- in the set analysis that you are using?

Re: Set Analysts Exclude problem

Anonymous — Tue, 05 Jul 2016 06:50:50 GMT

Hi.

Which two values you want to exclude.??

Thanks

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Tue, 05 Jul 2016 06:57:28 GMT

I have table like below

name

rep

Score

AlanYoung

AllisonMcMurray

130

AlanYoung

140

AmjadMahmood

Sales Objective Category

AlanYoung

Key Message Delivery

AmjadMahmood

Here, I want to create one funnel chart, With the key of whoever received the highest avg score.

I mean,

1) >70 is one

2) <70 and >50

3) <50

Note: I have taken calc dim -

I created one variable - vAverage = Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score])

Dimension

=Aggr(If($(vAverage) < 50, '<50',

If($(vAverage) > 70, '>70',

'50-70')),[rep])

Expression

vAverage

Expression BG Color

=If(Avg({$<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score]) >=70,RGB(255,0,0),

if(Avg({$<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score]) <70 and Avg({<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score]) >50 ,RGB(0,255,0),

if(Avg({$<[2014 name] = {*}-{'Key Message Delivery','Sales Objective Category'}>} [2014 Score]) <=50 ,RGB(255, 165, 0)

)

Now, I want to know is that expression is correct one or not?

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Tue, 05 Jul 2016 07:00:49 GMT

Kishore,

I want to exclude these two 'Key Message Delivery','Sales Objective Category'

Re: Set Analysts Exclude problem

Anonymous — Tue, 05 Jul 2016 07:16:05 GMT

Hi,

the expression looks correct.

I might suggest, since you have a variable already, you can try this:

=If(vAverage >=70,RGB(255,0,0),

if(vAverage <70 and vAverage >50 ,RGB(0,255,0),

if(vAverage <=50 ,RGB(255, 165, 0)

)

Makes a bit more readable

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Tue, 05 Jul 2016 07:19:03 GMT

In my real, I am using same unfortunately my client told me this is wrong, Just think and how and why?

- Anil

Re: Set Analysts Exclude problem

Anil_Babu_Samineni — Tue, 05 Jul 2016 10:10:20 GMT

How about this,

Avg({$<[2014 rep] = {"=[2014 name] =- {'Key Message Delivery','Sales Objective Category'}"}>} [2014 Score])

Here, I want to share my logic

I want to know average score which based at 2014 rep values and then meantime, I want to exclude those two values.

It showing No data to display

Please help me.