topic Re: Consecutive count in QlikView

Consecutive count

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Consecutive count

Anil_Babu_Samineni — Sat, 06 Jan 2018 02:00:14 GMT

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

Re: Consecutive count

petter — Sat, 06 Jan 2018 21:15:12 GMT

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.

Re: Consecutive count

Anonymous — Sun, 07 Jan 2018 00:31:44 GMT

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.

Re: Consecutive count

Anonymous — Sun, 07 Jan 2018 00:47:17 GMT

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.

Re: Consecutive count

petter — Sun, 07 Jan 2018 00:54:29 GMT

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.

Re: Consecutive count

petter — Sun, 07 Jan 2018 01:19:49 GMT

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.

Re: Consecutive count

Anil_Babu_Samineni — Sun, 07 Jan 2018 02:57:11 GMT

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

Re: Consecutive count

Anonymous — Tue, 09 Oct 2018 08:23:04 GMT

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?

Re: Consecutive count

Anil_Babu_Samineni — Wed, 10 Oct 2018 03:49:33 GMT

Check there Count consecutive months.