topic Re: only show zero values in the expression in QlikView

only show zero values in the expression

Anonymous — Fri, 24 Oct 2014 13:50:54 GMT

Hi Qlikview community

I have one issue I wanted to solve

Here below I have the database I have.

In this database I see the cost (total sum) for ever Job Ref.

I want to monitor this cost for every job ref.

But I have only interest in the job ref which has zero.

So in my table in qlikview how can let my chart only show Total sum that has value zero and nothing else

Shipping terms point	Job Ref	Quantity	Total Sum
CNTAO	AAR155296	4	91056,76
DECUX	AAR155297	3	0
DECUX	AAR155299	4	0
DKAAR	AAR155300	1	0
DKAAR	AAR155301	3	0
DKAAR	AAR155302	5	0
DKAAR	AAR155303	13	0
GBBRF	AAR155304	1	116748,26
GBIMM	AAR155306	6	9461,66
GBIMM	AAR155307	1	45839,55
GBIMM	AAR155308	23	44672,46
GBIMM	AAR155309	19	68742,14

regards Darri Hilmarsson

Re: only show zero values in the expression

m_woolf — Fri, 24 Oct 2014 14:27:45 GMT

Try:

=if(aggr(sum([Total Sum]),[Job Ref])=0,[Job Ref])

as a calculated dimension for [Job Ref]

Re: only show zero values in the expression

Fri, 24 Oct 2014 14:32:40 GMT

try something like this:

sum({$<[Total Sum]={'0'}>} [Total Sum])

Re: only show zero values in the expression

Anonymous — Fri, 24 Oct 2014 14:53:33 GMT

Hi, guys

I tried both solutions but it did not work actually like I want

In the chart below in QlikView I want see the bold lines and the other don't show

The picture below shows the result with the formulas you gave me.

The line are still there but I want them to disappear

Job Ref	Quantity	Shipping terms point	=sum([Total Sum])
			1.160.277
AAR155300	1	DKAAR	0
AAR155322	1	NLRTM	0
AAR155323	1	NLRTM	0
AAR155325	1	NLRTM	0
AAR155297	3	DECUX	0
AAR155301	3	DKAAR	0
AAR155299	4	DECUX	0
AAR155302	5	DKAAR	0
AAR155312	5	GBIMM	0
AAR155324	5	NLRTM	0
AAR155313	8	GBIMM	0
AAR155303	13	DKAAR	0
AAR155306	6	GBIMM	9.462
AAR155308	23	GBIMM	44.672
AAR155307	1	GBIMM	45.840
AAR155315	2	KRPUS	46.101
AAR155309	19	GBIMM	68.742
AAR155318	1	NGONN	78.489
AAR155319	1	NGONN	78.942
AAR155310	23	GBIMM	90.660
AAR155296	4	CNTAO	91.057
AAR155320	1	NLRTM	92.119
AAR155314	1	KEMBA	94.442
AAR155304	1	GBBRF	116.748
AAR155316	1	KRPUS	136.558
AAR155317	2	NGAPP	166.445

Quantity	Shipping terms point	=if(aggr(sum([Total Sum]),[Job Ref])=0,[Job Ref])	=sum([Total Sum])
			1.160.277
3	DECUX	AAR155297	0
4	DECUX	AAR155299	0
1	DKAAR	AAR155300	0
3	DKAAR	AAR155301	0
5	DKAAR	AAR155302	0
13	DKAAR	AAR155303	0
5	GBIMM	AAR155312	0
8	GBIMM	AAR155313	0
1	NLRTM	AAR155322	0
1	NLRTM	AAR155323	0
5	NLRTM	AAR155324	0
1	NLRTM	AAR155325	0
1	GBBRF	-	116.748
1	GBIMM	-	45.840
1	KEMBA	-	94.442
1	KRPUS	-	136.558
1	NGONN	-	157.431
1	NLRTM	-	92.119
2	KRPUS	-	46.101
2	NGAPP	-	166.445
4	CNTAO	-	91.057
6	GBIMM	-	9.462
19	GBIMM	-	68.742
23	GBIMM	-	135.333

Re: only show zero values in the expression

Fri, 24 Oct 2014 14:58:35 GMT

Darril, can you attach the .qvw file?

Re: only show zero values in the expression

Anonymous — Fri, 24 Oct 2014 15:05:01 GMT

here it comes with you expression and not

Re: only show zero values in the expression

tabletuner — Fri, 24 Oct 2014 15:05:17 GMT

Alfredos formula should do the job for you, however you still have got to remove the original =sum([Total Sum]) formulas. That formula will reactivatie the non zero Job Refs again.

Re: only show zero values in the expression

Fri, 24 Oct 2014 15:11:08 GMT

Here darril try this:

=if([Total Sum]=0, [Job Ref])