I am working to enhance a dataset by creating a column that would allow me to track how many active quarters a given company has had for a given row. A company is "active" if they recognize revenue within that quarter. Each row of my dataset represents one month's performance for a single company.
I have been able to use a WINDOW function to create a running sum for active months successfully:
COUNTIF(Revenue IS NOT NULL) OVER
(partition by Company_Name ORDER BY month_end ASC ROWS BETWEEN unbounded preceding and current row) AS cumulative_active_months
I am now struggling to convert my logic to count the quarters rather than the months.
This is a rough idea of what my table currently looks like.
Row Month Month_end Fiscal_Quarter Company_Name Revenue Active month count
----- ------- ------------ ---------------- -------------- --------- --------------------
1 Jul 2016-07-31 FY17-Q2 Foo x,xxx 1
2 Jul 2016-07-31 FY17-Q2 Bar xxx,xxx 1
3 Aug 2016-08-31 FY17-Q2 Foo xx,xxx 2
4 Aug 2016-08-31 FY17-Q2 Bar xxx 2
5 Sep 2016-09-30 FY17-Q2 Foo xx 3
6 Sep 2016-09-30 FY17-Q2 Bar x,xxx 3
7 Oct 2016-10-31 FY17-Q3 Foo xx 4
8 Oct 2016-10-31 FY17-Q3 Bar Null 3
This what ideally I'd like for my table to look like.
Row Month Month_end Fiscal_Quarter Company_Name Revenue Active month count Active quarter count
----- ------- ------------ ---------------- -------------- --------- -------------------- ----------------------
1 Jul 2016-07-31 FY17-Q2 Foo x,xxx 1 1
2 Jul 2016-07-31 FY17-Q2 Bar xxx,xxx 1 1
3 Aug 2016-08-31 FY17-Q2 Foo xx,xxx 2 1
4 Aug 2016-08-31 FY17-Q2 Bar xxx 2 1
5 Sep 2016-09-30 FY17-Q2 Foo xx 3 1
6 Sep 2016-09-30 FY17-Q2 Bar x,xxx 3 1
7 Oct 2016-10-31 FY17-Q3 Foo xx 4 2
8 Oct 2016-10-31 FY17-Q3 Bar Null 3 1