topic Re: How to calculate consecutive months count in QlikView

How to calculate consecutive months count

NavinReddy — Fri, 24 Jun 2016 11:39:02 GMT

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,

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

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

)))))))

Any suggestions

Thanks,

Niru

Re: How to calculate consecutive months count

sunny_talwar — Fri, 24 Jun 2016 11:42:36 GMT

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

Re: How to calculate consecutive months count

NavinReddy — Fri, 24 Jun 2016 11:50:16 GMT

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

Re: How to calculate consecutive months count

varshavig12 — Fri, 24 Jun 2016 11:54:09 GMT

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

Re: How to calculate consecutive months count

NavinReddy — Fri, 24 Jun 2016 12:03:54 GMT

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

Re: How to calculate consecutive months count

rubenmarin — Tue, 28 Jun 2016 07:27:34 GMT

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

Re: How to calculate consecutive months count

NavinReddy — Tue, 28 Jun 2016 07:36:54 GMT

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

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

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

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

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)

Re: How to calculate consecutive months count

NavinReddy — Tue, 28 Jun 2016 07:40:35 GMT

Many Thanks

Re: How to calculate consecutive months count

NavinReddy — Wed, 03 Aug 2016 22:05:46 GMT

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

Re: How to calculate consecutive months count

NavinReddy — Mon, 08 Aug 2016 05:40:32 GMT

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