aggregation issue

[bobbydave]

=

Concat(

distinct aggr(

  if(

   Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

         [The Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

      [Record Count])

  // if this month equals 0, throw back the country name

   =0, 

   Country & ' ' & [Control Description]  & ' is zero.' ),

  Country  , [Control Description] ), chr(13))

I have the above expression that should throw back anything from last month where [Record Count] =0

This will throw back ALL countries and ALL [Control Description] values.

I only want it should equate to. So I have in my set analysis to remove [Control Description] = {'Avaya'}

but this appears in my result.

I then tried

[Control Description] <> 'Avaya' towards the end of my aggregation which ONLY gives me what has Avaya as Control Description where in fact, it should be showing me everything but avaya.

Any ideas?

=

Concat(

distinct aggr(

  if(

   Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

         [The Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

      [Record Count])

  // if this month equals 0, throw back the country name

   =0, 

   Country & ' ' & [Control Description]  & ' is zero.' ),

  Country  , [Control Description] <> 'Avaya'), chr(13))

[MK_QSL]

IF([Control Description <> 'Avaya',[Control Description)

[MK_QSL · Accepted solution]

I think this is what you need..

=Concat({<[Control Description] -= {'Avaya'}>}

distinct aggr(

  if(

   Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

         [The Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

      [Record Count])=0,

   Country & ' ' & [Control Description]  & ' is zero.' ),

  Country  ,[Control Description]), chr(13))

or

=Concat(

{< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

         [The Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

distinct aggr(

  if(

   Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

         [The Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

      [Record Count])=0,

   Country & ' ' & [Control Description]  & ' is zero.' ),

  Country  ,[Control Description]), chr(13))

[bobbydave]

The top part worked a gem.

Thanks