Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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
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
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
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
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))
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)
Many Thanks
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
Best Regards,
Niru
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