topic Re: Exclude values in Chart in QlikView

Exclude values in Chart

Thu, 10 Dec 2015 13:29:03 GMT

I have a chart object (table) with result over the last 5 years per year.

In this table the revenue is displayed per year over the last 5 years per customer.

I now want to hide al customers that didn't buy anything in 2014,2013,2012, 2011 so I want to show only the customer with revenue in 2015.

Any suggestions on how to do this?

Re: Exclude values in Chart

jcampbell474 — Thu, 10 Dec 2015 15:55:21 GMT

Maybe I'm not understanding you correctly, but can you try placing a check in the Presentation Tab > Suppress Missing checkbox? Perhaps the Dimensions > Suppress when Value is Null, too?

Re: Exclude values in Chart

rubenmarin — Thu, 10 Dec 2015 16:54:39 GMT

Hi Chris, maybe using a calculated dimension for Customer, i.e.:

=Aggr(If(Sum({<YearField={'>$(=Year(Today())-5)<$(=Year(Today()))'}>} SalesField), CustomerField), CustomerField)

And checking 'Supress when value is null'. That will filter the customers with Sales<>0 between 2011 and 2014

Re: Exclude values in Chart

Thu, 10 Dec 2015 18:01:27 GMT

Maybe it is easier with an example

This is the table out put as it is

Klant	calculated sum revenu 2015	Calculated sum revenu 2014	Calculated sum revenu 2013	Calculated sum revenu 2012	Calculated sum revenu 2011
A	1000	1000	800	700	850
B	1010	1000	850	750	800
C	1020	1000	850	750	850
D	1030	1000	725	750	800
E	1040	1000	0	0	0
F	1050	1000	0	0	0
G	1060	1000	1200	1000	1100
H	1070	1000	0	0	0
I	1080	1000	400	550	400

And I want the output to show only the new customers like this

Klant	calculated sum revenu 2015	Calculated sum revenu 2014	Calculated sum revenu 2013	Calculated sum revenu 2012	Calculated sum revenu 2011
E	1040	0	0	0	0
F	1050	0	0	0	0
H	1070	0	0	0	0

Re: Exclude values in Chart

Thu, 10 Dec 2015 18:04:40 GMT

I added an example, can you check this?

Re: Exclude values in Chart

Thu, 10 Dec 2015 18:05:13 GMT

I added an example which show maybe better what I want to do?

Re: Exclude values in Chart

rubenmarin — Fri, 11 Dec 2015 07:27:32 GMT

Hi Chris, I can't see the example (It's an uploaded sample?) but this last post is asking the opposite of what I understand, try if this calculated dimension works.

=Aggr(If(Sum({<YearField={'>$(=Year(Today())-5)<$(=Year(Today()))'}>} SalesField)=0, Klant), Klant)