Aggr

[bobbydave]

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?

[sunny_talwar · Accepted solution]

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