Hi Betina,
Please use the below query to get rolling thirteen months starting from current month, and use singleton to display the dynamic rolling past 13 months in the column header.
select '01' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select getdate() as "C1") Q1 union
select '02' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -1,day(getdate())-1) as "C1") Q1 union
select '03' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -2,day(getdate())-1) as "C1") Q1 union
select '04' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -3,day(getdate())-1) as "C1") Q1 union
select '05' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -4,day(getdate())-1) as "C1") Q1 union
select '06' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -5,day(getdate())-1) as "C1") Q1 union
select '07' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -6,day(getdate())-1) as "C1") Q1 union
select '08' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -7,day(getdate())-1) as "C1") Q1 union
select '09' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -8,day(getdate())-1) as "C1") Q1 union
select '10' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate()) -9,day(getdate())-1) as "C1") Q1 union
select '11' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate())-10,day(getdate())-1) as "C1") Q1 union
select '12' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate())-11,day(getdate())-1) as "C1") Q1 union
select '13' "sn", datename(m,Q1.C1)+' '+datename(year,Q1.C1) "month" from (select dateadd(month,datediff(month,0,getdate())-12,day(getdate())-1) as "C1") Q1
Thanks,
CD
------------------------------
Chakra Ganesh Cognos Admin
Cognos Admin
Indianapolis IN
------------------------------
Original Message:
Sent: Tue May 26, 2020 03:17 PM
From: Albert Valdez
Subject: Changing calculated column from displaying current month - 1 to the name of the specific month/year
Hi Betina,
If you are calculating the prior months using something like add_months(current_date,-1), etc., then your results should return an integer; 1 through 12.
You will need to enclose those values with a conditional that maps the integers to the month names, for example: if([Prev. Month Number] = 1) then (January) else... and so on. You'll have to copy that logic into each of the 13 month expressions. Create this is a separate data item so you can still use the month numbers for your sort criteria.
The Year will have also be calculated independently and is actually a bit trickier. If you can get away without the year, hopefully this helps you solve for month names and you can move on. I'll continue thinking on the year logic, but maybe somebody else will chime in with an existing solution.
------------------------------
Albert Valdez
albert@intelalytics.comAlbert Valdez
albert@intelalytics.com
Original Message:
Sent: Fri May 22, 2020 01:43 PM
From: Betina Stapleton
Subject: Changing calculated column from displaying current month - 1 to the name of the specific month/year
I have a report in which I've created 13 calculated columns that return current month -1, current month -2, current month -3, etc. This report includes rolling 13 months. How can I change the column headings so instead of current month - 1 it displays the actual Month and Year. So, for example, current month -1 would equal April 2020, current month -2 would equal March 2020, etc. These will change each month that the report is run. so in June current month -1 would show May 2020, etc.
Any help is appreciated.
------------------------------
Betina Stapleton
------------------------------
#CognosAnalyticswithWatson