Retrieve Prior 12 Months as Rolling 12

[Cognos_Jan2017]

Relational database, Cognos 10.2.2.  Package does not contain "dates" other than months, calendar and fiscal years.

Not sure a Date Prompt will retrieve previous 12 months w/ correct calendar year(s).

Can always provide 2 prompts of Calendar Year and Months ... forcing User to click appropriate
checkboxes, but that's not ideal.

With NO "dates" in the Package, does anyone have, in their Knowledge Base, a way to retrieve,
IE, from a Date Prompt selecting May 11, 2017 to get ...
May 2017
April 2017
March 2017
February 2017
January 2017
December 2016
November 2016
October 2016
September 2016
August 2016
July 2016
June 2016

TIA, Bob

[tjohnson3050]

Checkout the _make_timestamp function.  It takes three integers for year, month and day.  You can hard code the day as 1, so every record should have a date for the first of that month.

Then use _months_between(current_date,XXX) <= 12 or something like that for your rolling 12 month filters. (Where XXX is the date you build above)

[Cognos_Jan2017]

Thank you tjohnson3050.

I got diverted to another Report, and to do this justice, I will have
to try this next Monday.  I will post back how I do w/ that.

Have a good weekend, and thanks again, Bob

[Cognos_Jan2017]

tjohnson3050 - We have a cross tab of one Measure w/ Nested Items of Fiscal Year, Month, and Calendar Year.

Where do we add a Data Item (correct?) to record the _make_timestamp?  We will call that _make_timestamp 'RecordDate'

Once that is placed, where does the _Months_between(Current_Date, 'RecordDate') go?  Does that serve as a Filter?

TIA, Bob

[Cognos_Jan2017]

Expanding on this, we also want to build a 24-month rolling 12 which would be a List Report.

Its Title would be a Selected Category, w/ Locations on the left side.

Then we would breakdown each of the 12-months into something like "R12 FY 2017" and 'R12 FY 2016',
adding Query Calculations from the two defined 12-months.

TIA, Bob

[Cognos_Jan2017]

Am continuing to play with this by ...

Our Fiscal Year begins in October.  IE, first month of FY 2017 is October 2016

When a Detail Filter (where Current_Date is 15 May) is ...
([Fiscal Year] = year(Current_Date) and [Fiscal Year Month #] <= 12 )

This translates to ...
([Fiscal Year] = 2017 and [Fiscal Year Month #] = 8 )

... which gives result ...
2017
October  November  December  January  February  March  April  May
2016   2016    2016    2017      2017      2017   2017  2017

Needed result is ...
The above 8 months, plus ...
Fiscal Year 2016
  ... June 2016
  ... July 2016
  ... August 2016
  ... September 2016

Will continue adjusting a detail filter based on Calendar and/ or Fiscal Years.

Thoughts welcomed.

TIA, Bob

[tjohnson3050]

tjohnson3050 - We have a cross tab of one Measure w/ Nested Items of Fiscal Year, Month, and Calendar Year.

Where do we add a Data Item (correct?) to record the _make_timestamp?  We will call that _make_timestamp 'RecordDate'

Once that is placed, where does the _Months_between(Current_Date, 'RecordDate') go?  Does that serve as a Filter?

TIA, Bob

Add the make timestamp function as a data item in the query you use for the report object, and put the months between function as detail filter in the same query.

[tjohnson3050]

Expanding on this, we also want to build a 24-month rolling 12 which would be a List Report.

Its Title would be a Selected Category, w/ Locations on the left side.

Then we would breakdown each of the 12-months into something like "R12 FY 2017" and 'R12 FY 2016',
adding Query Calculations from the two defined 12-months.

TIA, Bob

So to mix time periods in the same report, you don't use a detail filter, but use case statements inside separate data items.  For example:

Case when _months_between(current_date, [RecordDate]) <= 12 then [measure] else 0 end

Set the aggregate properties to 'Total' and this will data item will give you a sum of that measure for that rolling 12 months.  Duplicate this data item for any other time periods, and replace current_date with a different date function for the prior year rolling 12.  For example:

Case when _months_between(_add_years(current_date,-1), [RecordDate]) <= 12 then [measure] else 0 end

[Cognos_Jan2017]

Wll try both and post back my results.

Thank you, Bob

[Cognos_Jan2017]

tjohnson3050, for ...

Add the make timestamp function as a data item in the query you use for the report object, and put the months between function as detail filter in the same query.

That WORKS if I detail filter the MonthsBetween Data Item as <= 11.  That results in Months ...
June 2016, July 2016, ... , May 2017.

One unexpected result ... it adds Decenber 2017 to the far right.  Any idea
why that might occur?

TIA, Bob

[BigChris]

One unexpected result ... it adds Decenber 2017 to the far right.  Any idea
why that might occur?

Logically, that would suggest that there's some data that's been stamped with a date in December. You can probably remove it from your report (assuming you can't get the raw data corrected) by changing your filter to something like:

Code Select Expand

[MonthsBetween] between 0 and 11

[Cognos_Jan2017]

BigChris - Yes, there was an entry in December 2017.  Strange because the value
entered in December 2017 was the same value entered in December 2016.

Ny filter had been ...
[MonthsBetween] <= 11

Changing it to your recommendaion of ...
[MonthsBetween] between 0 and 11

WORKS.

Not sure why, but I will learn from it.

THANK you BigChris, Bob

[BigChris]

The reason why is that your MonthsBetween calculation will have brought back a negative number for December 2017. The changes to your filter will remove anything where the month of your RecordDate is in the future.

[Cognos_Jan2017]

I understand now.

Thank you BigChris.

[Cognos_Jan2017]

tjohnson3050

So to mix time periods in the same report, you don't use a detail filter, but use case statements inside separate data items.  For example:

Case when _months_between(current_date, [RecordDate]) <= 12 then [measure] else 0 end

Set the aggregate properties to 'Total' and this will data item will give you a sum of that measure for that rolling 12 months.  Duplicate this data item for any other time periods, and replace current_date with a different date function for the prior year rolling 12.  For example:

Case when _months_between(_add_years(current_date,-1), [RecordDate]) <= 12 then [measure] else 0 end

Someone wrote the only dumb question is the one that isn't asked.  I do feel dumb, but I will ask ...

The "[measure]" I am using examines Units for Gallons, Kilograms, and Pounds, leaves Pounds as Pounds, and
converts Gallons and Kilograms to Pounds. It becomes a Data Item named "In_Pounds"

The Data Item "In_Pounds" ...
Case
When [Sustainability Performance DataMart].[Units Facility].[Facility UOM]='Gallons' Then [Sustainability Performance DataMart].[Fact Location Data].[Facility Data]*7
When [Sustainability Performance DataMart].[Units Facility].[Facility UOM]='Kilograms' Then [Sustainability Performance DataMart].[Fact Location Data].[Facility Data]*2.20462
When [Sustainability Performance DataMart].[Units Facility].[Facility UOM]='Pounds' Then [Sustainability Performance DataMart].[Fact Location Data].[Facility Data]
End

I can set the aggregate to "Total" for that, but trying a Data Item of "R12" with ...

Case when _months_between(current_date, [RecordDate]) <= 12 then In_Pounds
else 0
end

... fails.

HELP ... TIA, Bob

[Cognos_Jan2017]

I am a dummy.

Had to leave the office to realize I didn't fully qualify the path.

This looks good ...
Case
When [Q_SolidWaste_9May].[GoGoMonths] between 0 and 11 Then [Q_SolidWaste_9May].[In_Pounds]
End

THANKS to all here who help us less-knowledgable.

[tjohnson3050]

Well, the syntax looks right.  What is the error message you are getting?

[Cognos_Jan2017]

I was offline thru today.  The fully-qualified path corrected the problem.

I don't even remember the exact error message but knew I had messed-up somewhere ...
the somewhere being the need to utilize the Query "[Q_SolidWaste_9May]" as a prefix.

Again, the Cognoise Guys delivered great results.

THANKS again, Bob