Cognos Analytics

 View Only
Expand all | Collapse all

Changing calculated column from displaying current month - 1 to the name of the specific month/year

  • 1.  Changing calculated column from displaying current month - 1 to the name of the specific month/year

    Posted Fri May 22, 2020 01:43 PM
    ​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


  • 2.  RE: Changing calculated column from displaying current month - 1 to the name of the specific month/year

    Posted Tue May 26, 2020 03:18 PM
    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
    ------------------------------



  • 3.  RE: Changing calculated column from displaying current month - 1 to the name of the specific month/year

    Posted Thu May 28, 2020 02:14 PM
    ​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
    ------------------------------