data between the last 8 months and 2 months

[bobbydave]

I'm gathering 6 months of data (not taking last month into consideration but the months previous.

Anyone see any issue below?

=  Concat(DISTINCT{< Country ={"=Sum({<[Control Description], Country,

    [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])   & 'is more than 3000'& chr(13))

[Anil_Babu_Samineni]

What is your intend to show, What you want to get in between of these days

I assume, Today is 19-12-2016 and you want to get [Record Count] between these dates >= 19-04-2016 <= 19-10-2016 and then what are you trying. Can you describe more of your exact issue. I mean you want aggregation values or some concat for [Record Count]

[bobbydave]

I'll start from the start.

What I am trying to do is create a condition in an Alert that will

A) sum([Record Count]) for last month

and also to get the

B) sum([Record Count]) for as you said Apr 2016 through to Oct 2016)

I am then going to divide these two numbers and if the Condition is greater than 20%, give me the alert, otherwise all is working fine and i get no alert.

=Concat(

DISTINCT {<Country = {"=Sum( {< Country , [Control Description] = {'Claims Registered'},

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

   Sum({< Country, [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 & '  is the issue' & chr(13))

[PrashantSangle]

Hi,

need to modify your set expression and 'and ' wont help you in set analysis

try like below example

sum({<date={">=(=date(addMonths(max(dateField),-8))<=(=date(addMonths(max(dateField),-2))"}>}sales)

Regards

[bobbydave]

= Concat( DISTINCT {<Country = {"= Sum({< Country, [Control Description] = {'Claims Registered'},

     sum({[Fiscal Year Month] = {">=(=date(AddMonths(max(today(),-8))<=(=date(AddMonths(max(today(),-2))"}>}

  [Record Count]) > 0.2"}>}  Country)

I seem to have made a bigger mess

[PrashantSangle]

Hi,

I have just gave you example modify it as per your requirement.

Regards,