Fiscal Year Query Creation to Convert Calendar to Fiscal Year Cognos

[HelloCognos]

Hi, :) :)

I'm trying to find a good explanation and approach in dealing with Fiscal Year calculation and filtration of data. So, having a bucket established
that it'll take the data such as following and show it after calculations based on the fiscal year.

CAR ID              CAR SOLD       
1111                10/02/2017

So, fiscal year wise, how do we get our Query to treat the above as a fiscal year and not calendar year.
Thanks a lot@

[Lynn]

Hi, :) :)

I'm trying to find a good explanation and approach in dealing with Fiscal Year calculation and filtration of data. So, having a bucket established
that it'll take the data such as following and show it after calculations based on the fiscal year.

CAR ID              CAR SOLD       
1111                10/02/2017

So, fiscal year wise, how do we get our Query to treat the above as a fiscal year and not calendar year.
Thanks a lot@

The best approach for this is to have a date dimension in your data warehouse to hold all the various date representations that are commonly needed in reporting and analytics. The brief Kimball article link below provides a general explanation. In addition to various representations of a date it is also useful to include relative date information. These are updated nightly and are useful to identify today, tomorrow, yesterday, this week, last week, last month, this financial week, last financial week, last financial period, etc.

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/calendar-date-dimension/

[HelloCognos]

Thanks for the information,

My apologies. I meant to say Relational for our Framework at least for now.

Thanks

[Lynn]

Thanks for the information,

My apologies. I meant to say Relational for our Framework at least for now.

Thanks

This applies to relational models. Trust me, you want a date dimension.

Data warehouse terms refer to facts and dimensions organized in a star schema. When developing a data warehouse and a relational Cognos model you will want to understand this. When you create a relational Cognos model over a non-data warehouse source you will still want to construct star schemas in order to get predictable SQL.

I highly recommend The Datawarehouse Toolkit.
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/

[HelloCognos]

Oh Wow,
Thanks so much Lynn!

[HelloCognos]

Hi Lynn,

Unfortunately, I was hoping that there is a way to setup logic in the Report to assign/filter certain dates from Calendar Year to Fiscal Year. I have certain dates that falls into 2018 Fiscal Year while the actual date is within Calendar Year.

Would the book/toolkit explain it for Report Analytics development building Query and Data Item Logic?

Thanks for your time as always

[adam_mc]

As Lynn points out, the suggested, and by far the best way to do this is to have a Date Dimension that you can use.

Remember, there is NO way that Cognos will know your particular Fiscal Calendar (it certainly doesn't know mine!) so you have to define it somewhere.

If you have to do this solely in the reporting layer, you can create a calculation that in effect creates the corresponding Fiscal Dates from the Calendar dates.
This will have to be a "massive" case statement that uses the Calendar Date and coverts it to the appropriate Fiscal Date.

Depending upon your use case and to what level you need to aggregate on, you may not need to define every date.
If you only need Fiscal Weeks, you can do 52/53 WHEN clauses with "date between..." to break out your Fiscal Weeks, similarly 12/13 WHEN's for Fiscal Months, or only a few WHEN's if you just need Fiscal Year.

However, you will have to change this as you move forward to next year and/or your requirements change.
So, again, consider having a Date Dimension created.

Hope this helps,
Adam.

[HelloCognos]

Yes, only fiscal year weeks (bi-weekly Payroll Payment) comparison is what I need. Could you please kind enough to send me a sample Case Statement for one of the scenarios? My apologies for taking more of your time. I'll talk to our DB and Framework Manager people for the Data Dimension but I'm sure it'll take them a while to come up with the necessary Dimensions.

Thanks so much!

[adam_mc]

Something like:

CASE
WHEN [Date] between FROM_DATE_1 and TO_DATE_1 THEN 'FW 201801'
WHEN [Date] between FROM_DATE_2 and TO_DATE_3 THEN 'FW 201802'
WHEN [Date] between FROM_DATE_3 and TO_DATE_3 THEN 'FW 201803'
WHEN [Date] between FROM_DATE_4 and TO_DATE_4 THEN 'FW 201804'
...
WHEN [Date] between FROM_DATE_52 and TO_DATE_52 THEN 'FW 201852'
END
 
You will need to put all the values in for the FROM_DATE's and TO_DATE's

Hope that is clearer,
Adam.

[HelloCognos]

Yes, thanks so much for your time. Great help!  :) :)
I think we will talk to our DBAs to get the fiscal year going as well,

[HelloCognos]

Hi Adam,
I know this has been a while and my apologies for getting back to you now.
So, it would be something like,
Something like:

CASE
WHEN [Date] between '2018-10-01' and '2018-10-30' THEN 'FW 201801' -- which is the first month of fiscal year?
...
WHEN [Date] between FROM_DATE_52 and TO_DATE_52 THEN 'FW 201852'
END
 
Thanks again