Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
=
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))
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))
IF([Control Description <> 'Avaya',[Control Description)
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))
The top part worked a gem.
Thanks