How to calculate consecutive months count

[NavinReddy]

Dear experts,

just want to calculate RAG_RATING_CD count if its continues more than 3 months i want to disply the count

if the value count should 3months is 1,

if its continues 4month count is 1 need to disply 4,

if its continues 5month count is 1 need to disply 5,

if its continues 6month count is 1 need to disply 6,

if its continues 7month count is 1 need to disply 7,

below expression im using to RAG_RATING_CD,its not working please help me where im doing mistake

=count(aggr(

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1,

and

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)=1,

and

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}Metric)=1,

and

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}Metric)=1,4,

or

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-120,'MMM YY'))"}>}Metric)=1,5,

or

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-150,'MMM YY'))"}>}Metric)=1,6

)))))))

Any suggestions

Thanks,

Niru

[sunny_talwar]

Is this a nested if statement or do you want to use 'and or' in a single if statement? You cannot use both... I am not entirely sure what you are trying to do to guide you with anything, but looking at the if statement syntax, you can def. make improvements here

[NavinReddy]

Hi Sunny, Thanks for your reply

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}Metric)=1

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}Metric)=1

count should be 3months 1, if its continuous coming month also

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-120,'MMM YY'))"}>}Metric)=1, 4,

i have to display count 4

sorry my logic is not working, please correct me how can i achieve is there any way

Many thanks,

Niru

[varshavig12]

And instead of using -30,-60 and -90 ,

you should subtract month directly,

month -1

month -2

and so on...

and then concat month and year.

like month&year to get MMYY

[NavinReddy]

Hi Varsha, Thanks for your reply

expression working fine, just want to calculate consecutive months count

if its more than 3monts continues just want to display sum of months number

not sure how to write logic

thanks,

Niru

[rubenmarin · Accepted solution]

Hi, trying to correct your syntaxis, I think it should be:

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1

and Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)=1

and Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}Metric)=1,

// If 3 firsts months = 1, check if 4th is also '1', else returns '3'

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}Metric)=1,

//If 4 firsts months=1, check if 5th is '1', else resturns '4'

   if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-120,'MMM YY'))"}>}Metric)=1,

    //If 5 firsts months=1 check if 6th is '1', then return '6', else returns '5'

    If(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-150,'MMM YY'))"}>}Metric)=1, 6, 5),

  4),

3))

[NavinReddy]

Hi Ruben,

your master its working, better than result what i expected, i can get 3 consecutive count also with your logic

Great Sir, Thank you somuch

i have developed code to achieve problem with very lengthy logic

if(Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}Metric)=1

and

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}Metric)=1

or

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-120,'MMM YY'))"}>}Metric)=1

or

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-150,'MMM YY'))"}>}Metric)=1

or

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-180,'MMM YY'))"}>}Metric)=1

or

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-210,'MMM YY'))"}>}Metric)=1,

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-30,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-60,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-90,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-120,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-150,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-180,'MMM YY'))"}>}Metric)+

Count(DISTINCT{<Month=,RAG_RATING_CD={'R'},MonthYear={"$(=Date($(vMonth)-210,'MMM YY'))"}>}Metric),0)

[NavinReddy]

Many Thanks

[NavinReddy]

Hi Ruben,

here i have posted one issue in side if i have used two match function one match function working fine as expected.

another macth function is not working. i think you are the right person to contact.

please help me

Expression Error

Best Regards,

Niru

[NavinReddy]

Hi Ruben,

Good Morning,

hope your doing good, i have posted one issue calculating 3 consecutive months.i din't get any reply.

I think you are the right person to contact.please help me.

Expression on display 3 Months data

Many Thanks,

Niranjan