topic What is the expression???!!! in QlikView

What is the expression???!!!

Fri, 14 Jan 2011 12:24:50 GMT

Hello to all,

I would like to know an expression that allows me to filter the top 40% of results (given that these results are already under an IF condition saved in a variable.) in a list or table if possible.

Thank you very much for any help and consideration.

What is the expression???!!!

Fri, 14 Jan 2011 12:50:14 GMT

Hi S, Could you post a sample of your document so I can see the specifics that you're working with. Thanks, S

What is the expression???!!!

Anonymous — Fri, 14 Jan 2011 13:14:05 GMT

If you only want to show the top 40%, a nice way to do this is to check the Max Number option in the presentation tab.
And use

=Count (DISTINCT FiledName)/100*40

Or if you want to make the percentage variabel use:

=Count (DISTINCT FiledName)/100*NameVariable

This way you can for example connect the variable to a slider-object.

What is the expression???!!!

Fri, 14 Jan 2011 13:16:19 GMT

Hi S,

Here it is....its seems foolish i know but i am incredibly rusty with this....

On the bottom left hand corner i am trying to do an analysis that lets me see the top 40% of countries that had an average overall satisfaction above 9, and the same for the list below that, for the 40% of countries that had scores between 0 - 4. I have one condition already limiting the data displayed to be that which falls under the condition instead of having trure/false values...but out of this filtered list i need the % filter if possible.

On another note, if i use the IF analysis with 8.5 or 7.5 it does not work....i tried comma as well but only accepts whole numbers.....

Thank you very much for taking the time S.

Regards,

What is the expression???!!!

Anonymous — Fri, 14 Jan 2011 14:27:49 GMT

Try this:

Expression:

avg([CQ1 - Overall Satisfaction])

Max Number (Presentation)

Count (DISTINCT [Customer Country])/100*40

By sorting the colom Satisfation descending or ascending you can get the top or the bottom 40 %

Is this what you are looking for?

What is the expression???!!!

Fri, 14 Jan 2011 14:54:43 GMT

Hi S,

My quick reply window closed i dont know if the message went through. I was saying that more or less it is what i am looking for except that I need a little step further. I managed to have appear on list the countries with scores above 9, and to not even show the countries that scored below. this i managed to get using:

(avg([CQ1 - Overall Satisfaction])>=9,avg([CQ1 - Overall Satisfaction]),0)

But from this list of filtered countries, I need a formula or expression or condition that only shows the top 40% of these countries. The 40% of countries that scored 9-10, not the whole list of countries that scored 9-10 is what i need. If i am not explaining well enough for you please let me know!

Thank you very much,

Salva

What is the expression???!!!

Anonymous — Fri, 14 Jan 2011 15:12:12 GMT

Is this working? :

if(avg([CQ1 - Overall Satisfaction])<=9,0,avg([CQ1 - Overall Satisfaction]))

🙂

What is the expression???!!!

Anonymous — Fri, 14 Jan 2011 15:29:49 GMT

This should work in combination with the Max Number function.

Let me know if you get it to work, if not I will try to upload a file later.

What is the expression???!!!

Fri, 14 Jan 2011 15:43:44 GMT

Hello Dennis,

It gives me the same result as my version of the IF( condition, so yes it does work! But i still need a way to filter only the top 40% of these, or bottom 40% of the lowest scores.

What is the expression???!!!

Fri, 14 Jan 2011 15:46:27 GMT

I tried them all, but the results do not change in the slightest bit. I appreciate all the ideas folks, and now Im determined to solve this!!!!!!!!

What is the expression???!!!

Anonymous — Fri, 14 Jan 2011 16:21:36 GMT

I see your point, you need to use the "if(avg([CQ...."-part in the MAx Number formule or in the dimension.
But I am not sure if that is possible in this way.

I will try more a little later....

What is the expression???!!!

Fri, 14 Jan 2011 16:39:31 GMT

Thank you Dennis. I will try the options again with this last one too. I appreciate your help and all you are doing you and everybody involved, Thanks!!