How to get countries from value.

[bobbydave]

=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

[sunny_talwar]

May be something like this:

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

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

[bobbydave]

That was exactly it.

Thanks Sunny

[bobbydave]

I am unable to mark this as correct

[sunny_talwar]

Awesome, I am glad I was able to help

[sunny_talwar]

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