Consecutive count

[Anonymous]

Hi guys,

I just want to count only the consecutive values based on maximum month-year (from descending order).

For example, 5 months are consecutive in the below screenshot based on the maximum month-year (Dec-2017) descending order.

Here we don't have Jul-2017 after Aug-2017. So, the count will be 5.

Attached sample QVW and data in excel. Please help.

Regards,

Siva.

[Anil_Babu_Samineni]

That means, you want to count only for Max 5 months? May be

=Count({<[Month-Year] = {">=$(=AddMonths(Max([Month-Year]),-5))<=$(=Max([Month-Year]))"}>} [Month-Year])

[petter]

You can use this expression to calculate the number of consecutive months starting from the last month:

=Index(Concat(Aggr(-(AddMonths(Below(total [Month Year],1),-1)=AddMonths(Below(total [Month Year],2),-2)),ID,[Month Year]),'',-[Month Year]),'0',2)-1

It can be put as it is in a text box or as a title or caption.

[Anonymous]

Thanks, Peter. It's working, but if year changes it's not working.

For example, after Jan-2018, it's not counting Dec-2017. Do we need to change the expression for it accordingly?

Also, I can't use this expression in straight table expressions. Please help.

[Anonymous]

Hi Anil,

I need to count consecutive months not upto maximum 5 months. I need to calculate the number of consecutive months starting from the last month in a straight table expression.

Thanks,

Siva.

[petter]

I tested the expression with various dates and it worked well for me across years. I tested it specifically with both Jan-2018 and earlier... Are your dates real dates? The dates I tested with are actual dates - every month is based on being the 1st of every month.

[petter]

If you want it to work on each row in a straight table you just have to add total in the Concat function:

Index(Concat(total Aggr(-(AddMonths(Below(total YM,1),-1)=AddMonths(Below(total YM,2),-2)),ID,YM),'',-YM),'0',2)-1

I have attached the QlikView application so you can have a look.

[Anil_Babu_Samineni]

With TOTAL key it will help? Chek Petter solution which is updated

[Anonymous]

Good day Guys

So sorry to resurrect this, please have mercy if im breaking any rules

How would the query change if the data and output changes the following?

[Anil_Babu_Samineni]

Check there Count consecutive months.