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.
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
:)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.
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.
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 :)
Glad it helped.
It is TRUE Cognoise.com accomplishes outstanding things.
AND ... Learning is a nonstop thing, IMHO, for all of us.
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.
Whoom "ceiling" Ok, I'll try that as well. Thanks a lot :)
that's faster as well
yes, this works fine too using the Ceiling . Thanks again!