topic How to get countries from value. in QlikView

How to get countries from value.

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

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

[Fiscal Year Month]= {'2016-11'} >} [Record Count])/Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] = {'Claims Registered'},

[Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>}

[Record Count]) < 0.2

The above set anallysis gives the result of 0.15 and what i am saying is 015 < 0.2

I am trying to write an expression to say:

If ( 0.15 < 0.2, give me the list of the countries that are less than 0.2, NULL()

i had

If ( 0.15 < 0.2, Concat(Country, ', '))

but this returns ALL the countries; whereas I only want the countries that are less than 0.2

Help appreciated

Re: How to get countries from value.

sunny_talwar — Fri, 09 Dec 2016 13:40:09 GMT

May be something like this:

Concat(If(Aggr(YourExpression, YourAggregatingDimension(s)) < 0.2, Country), ', ')

Re: How to get countries from value.

Anil_Babu_Samineni — Fri, 09 Dec 2016 13:41:36 GMT

You may use this

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

[Fiscal Year Month]= {'2016-11'} >} [Record Count])

Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] = {'Claims Registered'}, [Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8) and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>} [Record Count]) )< 0.2,

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

[Fiscal Year Month]= {'2016-11'} >} [Record Count])/Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] = {'Claims Registered'},

[Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>}

[Record Count]) < 0.2)))

Re: How to get countries from value.

sunny_talwar — Fri, 09 Dec 2016 13:41:46 GMT

May be this:

Concat(If(Aggr(

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

[Fiscal Year Month]= {'2016-11'} >} [Record Count])/Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] = {'Claims Registered'},

[Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>}

[Record Count]), Country) < 0.2, Country), ', ')

Re: How to get countries from value.

bobbydave — Fri, 09 Dec 2016 13:48:20 GMT

That was exactly it.

Thanks Sunny

Re: How to get countries from value.

bobbydave — Fri, 09 Dec 2016 13:49:18 GMT

I am unable to mark this as correct

Re: How to get countries from value.

sunny_talwar — Fri, 09 Dec 2016 13:49:35 GMT

Awesome, I am glad I was able to help

Re: How to get countries from value.

sunny_talwar — Fri, 09 Dec 2016 13:50:23 GMT

This thread was opened as a discussion and discussions don't have the option to mark correct. You can always mark helpful responses