topic How to filter data with complex condition? in QlikView

How to filter data with complex condition?

alexprost — Thu, 05 Aug 2010 11:11:31 GMT

Hi,

Here is a description of my problem:
- My script loads a list of bill, with customer name and amount. Each customer can have several bills.
- I have a simple table, with customer in dimension and Sum(amount) in expression

So I have the turnover for each customer and i have also the total turnover on first line.

I would like to filter only customers wich represent 80% of total turnover:
- first we sort customers on decreasing turnover
- then we accumulate turnover on each line
- we stop when the accumulation of turnover = 80% total turnover

Any idea ?

Thank you for help...

How to filter data with complex condition?

Thu, 05 Aug 2010 17:35:51 GMT

Hi Alex,

There is a simple way to do this.

You can display, in your chart, the customer which represent 80% of the overall.

I suggest you to add a button with a "PARETO SELECTION"

Do to this :

Create Button Object, add an action : Selection / Pareto Select

Then Fill :

Field = Customer

Expression = Sum( Sales)

Percentage = 80

Then when you click on the button, your chart is reduced to the 80% of sum(sales)

I posted an example.

http://community.qlik.com/forums/t/24027.aspx

JJJ

How to filter data with complex condition?

alexprost — Fri, 06 Aug 2010 11:24:49 GMT

Thak you very much Jean-Jacques ! The "pareto selection" seems to be the function I'm looking for.

I tried it quickly successfully. But I have now an "ergonomic" problem to solve:

- when the button is clicked , a selection on my customer is added in the current selection, but it's not very explicit ("51 of 11521" or list of customers). I'm not sure the users will understand if they have already clicked on the button or not.

- it's possible to click another time on the button, and then we see the 80% of the 80%... and users will be lost.

Alex

How to filter data with complex condition?

jonathandienst — Fri, 06 Aug 2010 12:01:33 GMT

Alex

Perhaps you could add a second action to Pareto button - one that sets a state variable to1.

Then you could change the chart lable to indicate "Pareto Selection On" (or whatever) or make a hidden textbox visible when the variable is 1.

You could even hide the Pareto button and replace it with a "Clear" button to reestset the selection.

That should make it clearer to the users what they have done and what they are looking at.

Just a suggetsion...

Jonathan

How to filter data with complex condition?

jonathandienst — Fri, 06 Aug 2010 12:02:21 GMT

Of course, the Clear button should also set the state variable back to 0!

Jonathan

How to filter data with complex condition?

jonathandienst — Fri, 06 Aug 2010 12:03:15 GMT

Of course the Clear button should also reset the state variable value back to 0!

Jonathan

Re. :Re: How to filter data with complex condition?

alexprost — Mon, 09 Aug 2010 14:53:33 GMT

Thank you Jonathan, your advices were helpful !