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!
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
Quote from: raj_aries81 on 21 Dec 2016 03:57:25 PM
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
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!
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!
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]