How can I return records that meet criteria 2 months IN A ROW?

[psrpsrpsr]

Hi folks, I need to return UserIDs that have 2 or more months IN A ROW with a FactCount value of >=10. I have no idea how to approach this in Cognos. Here is a sample table:

UserID     YearMonth     FactCount     Return in query?
5454          201601           5                     YES
5454          201602           3                     YES
5454          201603           11                   YES
5454          201604           10                   YES
5454          201605           6                     YES
98798        201601           2                     NO
98798        201602           0                     NO
98798        201603           12                   NO
98798        201604           5                     NO
98798        201605           7                     NO       

So far, I have a working query that returns all UserIDs that have >=1 month of FactCount. I achieved this with the following query structure:
Query 1: UserID, YearMonth, FactCount, FactCountFLAG (CASE WHEN FactCount >=10 FOR UserID, YearMonth THEN 1 END)
Query 2: Query1 with a filter of FactCountFLAG = 1
Query 3: Member IDs from Query1 INTERSECT Query 2
Query 4: Query 1 (1..n) INNER JOIN Query 3 (1..n)

But this does not give me the desired 2 or more months IN A ROW of FactCount>=10.

Any thoughts are greatly appreciated!

[raj_aries81]

I created a dataitem mod(YearMonth,2) and created two seperate queries with mod(YearMonth,2)=0 and mod(YearMonth,2)=1 filters, basically to seperate odd and even months and created a third query, joined on [userid]=[userid] and [YearMonth]=[YearMonth]+1 to check if they have consecutive months.

I havent tested all the possible scenarios esp when there are Dec and Jan as concecutive months within different years and there could be always a better way.

Good Luck !!

Regards
Raj

[raj_aries81]

I created a dataitem mod(YearMonth,2) and created two seperate queries with mod(YearMonth,2)=0 and mod(YearMonth,2)=1 filters, basically to seperate odd and even months and created a third query, joined on [userid]=[userid] and [YearMonth]=[YearMonth]+1 to check if they have consecutive months.

I havent tested all the possible scenarios esp when there are Dec and Jan as concecutive months within different years and there could be always a better way.

Good Luck !!

Regards
Raj

PS: Please check if you can use running-difference function

[psrpsrpsr]

Great input, thank you! I will take a stab at this when I am back in the office. I believe the underlying database is PostgreSQL. Any other input on approaches appreciated!

[psrpsrpsr]

raj_aries81, can you explain what you mean when you say you: "joined on [userid]=[userid] and [YearMonth]=[YearMonth]+1" ?

How do I add the YearMonth+1 in the join? Thanks!

[Andrei I]

Please see attached report example build against Cognos samples package [GO Data Warehouse (query)]

The report will output all Retailers who had over 30 orders per year for 2 years in a row
This example is using Years.

For Months   (MonthKey=YYYYMM)  filter will be a little more complicated.

Years => moving-total ([data].[Year],2 for [data].[Retailer]) -[data].[Year] +1= [data].[Year]

Months => moving-total ([data].[MonthKey],2 for [data].[Retailer]) -[data].[MonthKey] +1= [data].[MonthKey]
or moving-total ([data].[MonthKey],2 for [data].[Retailer]) -[data].[MonthKey] + 89= [data].[MonthKey]