topic Re: Put a specific filter on a chart ? in QlikView

Put a specific filter on a chart ?

Mon, 20 Jun 2011 08:21:59 GMT

Hi,

i have a chart with countries, month, note and sales

GB : 2011/01 : 4 : 1000

France : 2011/01 : 10 : 5000

Germany : 2011/01 : 8 : 6000

GB : 2011/02 : 4 : 1500

France : 2011/02 : 8 : 4000

Germany : 2011/02 : 7 : 7000

with many more countries.

I want to make a global bar graph sorted by sum(sales) with top 10.. Easy isn't it ?

But the problem is i don't want to see the countries for which the avg(note) is < 5

Thus, I absolutely don't want to see GB in this chart

how is it possible ?

Thanks

Re: Put a specific filter on a chart ?

IAMDV — Mon, 20 Jun 2011 09:00:06 GMT

Hi,

I had attached the sample QV document for your reference. You should use the below expression...

You need to use the below expression and sort the chart & restrict top 10 in Presentation Tab.

sum( {$<note ={'>5'}>} sales )

Hope this helps! Please let me know if you need further information...

Thanks,

Put a specific filter on a chart ?

Mon, 20 Jun 2011 11:18:33 GMT

nice answer but it's not exactly what i wanted : i need the avg(note) <5

for example if you change the criteria to

sum( {$<note ={'>=10'}>} sales )

you see only France in your example.

But in my request, even France should not appear because it has a note of 8 and a note of 10 and the average is <=10

i want to check the average note, not only the lines with a good note

i'm sure you can help me

thanks

Re: Put a specific filter on a chart ?

IAMDV — Mon, 20 Jun 2011 11:47:19 GMT

Oops! My mistake... I haven't read it completely. In that case, you can use simple IF statement, something like this...

IF(Avg(note) >= 10, sum(sales), 0)

Please do let me know if I am missing something!

Cheers - DV

Re: Put a specific filter on a chart ?

kaushiknsolanki — Mon, 20 Jun 2011 11:50:33 GMT

Hi,

Here is your solution.

Regards,

Kaushik Solanki

Re: Put a specific filter on a chart ?

IAMDV — Mon, 20 Jun 2011 12:11:32 GMT

I was about to provide same solution as Kaushik's. But I am little late now

Please use Kaushik's solutions compared to the one I had provided. Because Set Analysis is faster than the IF statements when you have huge dataset.

Cheers - DV