Rolling 12 Month Date Query (6 month Splits)

[t0mato]

Hello!

I have to run a report that captures all application data as a rolling 12 month aggregate. With a bit of help here from other users, we had discovered that the following code will accurately return rolling 12 month data:

[Date] >= _add_months (_first_of_month(Current_Date), -11)

The challenge I am having is as follows: My Application data report is too large to run and export from Cognos as we receive too many applications for the system to handle in 12 months of data. I've instead split the Application report into two reports, splitting the year (12 months) in half.

1. Application1: Captures all applications received from Jan 1 of the current year to Jun 30 of the current year.
2. Application2: Captures all applications received from Jul 1 of the current year to De 31 of the current year.

With these two separate reports, I union them together in Alteryx to get a final YTD report.

Since I have to split this data set in two, the aforementioned code to capture the rolling 12 month data will need to be adjusted. My question is, what date filters need to be applied to accurately return a rolling 12 month of data for a report that is split to capture 6 months at a time (since 12 at once will not run).

Thanks!

[MFGF]

Hello!

I have to run a report that captures all application data as a rolling 12 month aggregate. With a bit of help here from other users, we had discovered that the following code will accurately return rolling 12 month data:

[Date] >= _add_months (_first_of_month(Current_Date), -11)

The challenge I am having is as follows: My Application data report is too large to run and export from Cognos as we receive too many applications for the system to handle in 12 months of data. I've instead split the Application report into two reports, splitting the year (12 months) in half.

1. Application1: Captures all applications received from Jan 1 of the current year to Jun 30 of the current year.
2. Application2: Captures all applications received from Jul 1 of the current year to De 31 of the current year.

With these two separate reports, I union them together in Alteryx to get a final YTD report.

Since I have to split this data set in two, the aforementioned code to capture the rolling 12 month data will need to be adjusted. My question is, what date filters need to be applied to accurately return a rolling 12 month of data for a report that is split to capture 6 months at a time (since 12 at once will not run).

Thanks!

Hi,

The previous six months is just a small tweak on the filter you already have:

[Date] >= _add_months (_first_of_month(Current_Date), -5)

For the six months before that, you could try:

[Date] between _add_months (_first_of_month(Current_Date), -11) and _add_months (_last_of_month(Current_Date), -6)

Interesting that you find the data is too big - do you get an error? Alteryx is a great tool for data blending, and I'm wondering if you're trying to use Cognos for a task that Alteryx might be better suited to? Is there a reason you can't connect Alteryx to the data source your Cognos instance is using, and create a workflow to bring in the required data and deliver it to where it needs to go? It doesn't sound like you are using Cognos to visualize the data in this instance, so there might be a better and easier way to get the data file you need?

Just a thought.

MF.

[t0mato]

You are a genius. This works perfectly. I do get an error when running the report, however. It either times out in the system while running, or there's an error when downloading the file from Cognos (to the computer).