Aggr

bobbydave — Fri, 20 Nov 2020 18:02:30 GMT

I have an alert

Concat(
if(
aggr(sum({<[Control Description]},
[Fiscal Year Month] >}
[Record Count]),
Country)=0,
Country & ' ' & [Fiscal Year Month] & ' ' & [Control Description] & ': is reported as zero.' & chr(10)))

This brings back an alert to show a number of Countries, the date and the Control Description e.g.

If I select Control Description as Complaints then the result is:

Korea 2015-12 Complaints: is reported as zero.

Latvia 2015-12 Complaints is reported as zero

If I select Control Description as Measure then the result is:

Korea 2015-12 Measure: is reported as zero.

Latvia 2015-12 Measureis reported as zero

I don't want to have to select Control Description to show all countries and ALL Control Descriptions. I understand its the aggr function i would use but this screws up every expression I attempt.

Any ideas?

Re: Aggr

sunny_talwar — Wed, 27 Jan 2016 12:49:29 GMT

May be try this:

Concat(DISTINCT
Aggr(NODISTINCT

If(Sum(TOTAL <Country> {<[Control Description], [Fiscal Year Month]>} [Record Count]) = 0,
(Country & ' ' & [Fiscal Year Month] & ' ' & [Control Description] & ': is reported as zero.' & chr(10)),

Country, [Control Description])

)

Have not tested it out, so not 100% sure if it is going to work perfectly

topic Aggr in QlikView

Aggr

Re: Aggr