Topic Thread

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 5 days ago
    ​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
    ------------------------------


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

    Posted yesterday
    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
    ------------------------------