topic aggregation issue in QlikView

aggregation issue

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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))

Re: aggregation issue

MK_QSL — Mon, 09 Jan 2017 11:44:02 GMT

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

Re: aggregation issue

MK_QSL — Mon, 09 Jan 2017 11:46:29 GMT

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))

=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))

Re: aggregation issue

bobbydave — Mon, 09 Jan 2017 11:57:43 GMT

The top part worked a gem.

Thanks