topic Re: zero values in aggr() function in QlikView

zero values in aggr() function

pavan045 — Wed, 27 Apr 2016 06:05:10 GMT

Hi all,

I have some product and sales data. I created 2 straight table in which I am showing only 4 products sales at a time. see the picture below.

when I click next button those 2 straight tables show next 8 products.

for that in the dimensions of the straight table I am using below expressions

Straight table 1 caluculated dimension:

=if(Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales), 4), [Product Name])> ((8 * $(vTemp)) -8)

and

Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales), 4), [Product Name])< ((8 * $(vTemp)) - 3), [Product Name])

Straight table 2 caluculated dimension:

=if(Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales), 4), [Product Name])>= ((8 * $(vTemp)) - 3)

and

Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales), 4), [Product Name])< ((8 * $(vTemp)) + 1), [Product Name])

It worked good so far, but there is a small problem.

If a product don't have any sales in a year then that product is not showing up in the straight table at all. I want to show all the products in the straight table even if it's sales are zero.

any Ideas please..

Thank you.

Re: zero values in aggr() function

sunny_talwar — Wed, 27 Apr 2016 15:31:22 GMT

Can you make sure that 'Suppress When Zero' is unchecked on the presentation tab?

Re: zero values in aggr() function

swuehl — Wed, 27 Apr 2016 15:37:47 GMT

Try something like

=if(

Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales) +Sum({1} 0) , 4), [Product Name])> ((8 * $(vTemp)) -8)

and

Aggr(rank(sum({<Year = {'$(=vCurrentYear)'}>}Sales)+Sum({1} 0), 4), [Product Name])< ((8 * $(vTemp)) - 3)

, [Product Name])

Re: zero values in aggr() function

pavan045 — Thu, 28 Apr 2016 05:11:42 GMT

Yes sunny, I unchecked 'Supress Zero values' option. still it is not working.

Thank you.

Re: zero values in aggr() function

pavan045 — Thu, 28 Apr 2016 05:29:10 GMT

Thank you swuehl, Your answer is working fine.

Re: zero values in aggr() function

dunnalahk123 — Tue, 05 Mar 2019 07:30:05 GMT

HI Swuehl,

I am facing similar issue , could you help me please.

i am writing below expression

Num(Avg(Aggr(Avg([SVT GDC TAT NumValue]), [SVT GDC TAT ID])),'#,##0' ) when i do so values of zero is getting suppressed but where as i need Zero values as well for [SVT GDC TAT ID].

Re: zero values in aggr() function

dunnalahk123 — Tue, 05 Mar 2019 07:30:55 GMT

HI Swuehl,

I am facing similar issue , could you help me please.

i am writing below expression

Num(Avg(Aggr(Avg([SVT GDC TAT NumValue]), [SVT GDC TAT ID])),'#,##0' )

when i do so values of zero is getting suppressed but where as i need Zero values as well for [SVT GDC TAT ID]