If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Fiscal Year QTR in Relational Model Analytics

Started by HelloCognos, 08 Aug 2018 10:35:34 AM

Previous topic - Next topic

HelloCognos

Hi, :) :)
I'm troubling this site again.

I have a list of rows that shows the date for each transactions but I need to create the necessary Data Items so I can drop it in a List or Crosstab or Chart to Slice/Divide the data based on Fiscal Year Quarters. (QTR 1=Jan, Feb, March).

I was thinking to use a If Statement such as below but not sure about the full implementation and if there is a better way to do this.
extract (month, current_date)
if( [month] in (1, 2, 3) ) then ('Qtr1') else
if( [month] in (4, 5, 6) ) then ('Qtr2') else
if( [month] in (7, 8, 9) ) then ('Qtr3') else ('Qtr4')

Thanks a lot for your time.

Cognos_Jan2017

I usually utilize a Case When such as ...

Case
When [Month] IN ('January','February','March') Then 'Qtr 1'
When [Month] IN ('April','May','June') Then 'Qtr 2'
When [Month] IN ('July','August','September') Then 'Qtr 3'
When [Month] IN ('October','November','December') Then 'Qtr 4'
End

Your If else may also work.

Let us know.  Thank you, Bob

HelloCognos

 :)Case Statement looks better. Thanks I'll try it as a part of my Crosstab Top Edge to see if it's going to slice the data correctly.


Cognos_Jan2017

I programmed Access VBA for years and used LOTS of If Then Else ...

In Access, would sometimes use "Select Case", which works.

Within Cognos, I rarely utilize If else.  Case When is preferred, at least, by me.

Nesting your "Qtr' Data Item in the Top Edge of your crosstab should work.

You should be fine.

HelloCognos

Yes, thanks so much! I dropped the actual date and then the QTR Data Item under it and it is working nicely!!

This site is absolutely awesome!! The people are awesome too. We have to compete with some of easy way of doing things with MS BI these days. Having this site to provide the sample code and discussion allows us to be more competitive with today's BI Market. Our clients over here want quick result since other BIs that are not as good as Cognos are easier to show
things. That is just my personal opinion that the help of this site is extremely important to some of the Cognos guys. I understand the training is necessary as well. 

Thanks!! Go Cognos :)

Cognos_Jan2017

Glad it helped.

It is TRUE Cognoise.com accomplishes outstanding things.
AND ... Learning is a nonstop thing, IMHO, for all of us.

MFGF

Quote from: HelloCognos on 08 Aug 2018 10:35:34 AM
Hi, :) :)
I'm troubling this site again.

I have a list of rows that shows the date for each transactions but I need to create the necessary Data Items so I can drop it in a List or Crosstab or Chart to Slice/Divide the data based on Fiscal Year Quarters. (QTR 1=Jan, Feb, March).

I was thinking to use a If Statement such as below but not sure about the full implementation and if there is a better way to do this.
extract (month, current_date)
if( [month] in (1, 2, 3) ) then ('Qtr1') else
if( [month] in (4, 5, 6) ) then ('Qtr2') else
if( [month] in (7, 8, 9) ) then ('Qtr3') else ('Qtr4')

Thanks a lot for your time.

A far simpler and more efficient way to calculate the quarter number would be

ceiling([month] / 3)

This would use pure mathematics and would avoid if-then-else or case constructs altogether :)

Cheers!

MF.
Meep!

HelloCognos

Whoom "ceiling" Ok, I'll try that as well. Thanks a lot :)

sdf


HelloCognos

yes, this works fine too using the Ceiling . Thanks again!