Skip to Content
avatar image
Former Member

Current Report Month & Year calculation?

I have a cross tab which has Report Month = [1,2,3,4,5,6,7,8,9,10,11,12]

I also have Report Year = [2011, 2012, 2013, 2014, 2015, 2016]

Cross Tab -

| Jan | Feb | March | April Month to Date |

Facility |

I want to column header to read the month and display the month name. e.g 1 = Jan

Requirement is if the user runs the report for current year and month I want the column heading to read current month which in today's case is 'April MTD'

SO somehow we need to compare today's date with fields [Report Year] & [Report Month]

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 11, 2016 at 02:52 PM

    Hi Vishal,

    Create the below variables:

    • v_ReportMonthName

    =If([Report Month])=1) Then "Jan"

    ElseIf([Report Month]=2) Then "Feb"

    ElseIf([Report Month]=3) Then "Mar"

    ElseIf([Report Month]=4) Then "Apr"

    ElseIf([Report Month]=5) Then "May"

    ElseIf([Report Month]=6) Then "Jun"

    ElseIf([Report Month]=7) Then "Jul"

    ElseIf([Report Month]=8) Then "Aug"

    ElseIf([Report Month]=9) Then "Sep"

    ElseIf([Report Month]=10) Then "Oct"

    ElseIf([Report Month]=11) Then "Nov"

    ElseIf([Report Month]=12) Then "Dec"

    • v_CurrentMonthYTD

    =FormatDate(CurrentDate();"Mmm") + " YTD"

    • v_ReportMonthYTD

    =If(MonthNumberOfYear(CurrentDate())=[Report Month] And Year(CurrentDate())=[Report Year]) Then [v_CurrentMonthYTD] Else [v_ReportMonthName]

    And then, use the [Report Month] object itself what you already have to be displayed in the header row on top, and the trick is to create a Conditional Formatting on that Header row as, if [Report Month] = [Report Month] then format to show the font as White and Display with the formula = [v_ReportMonthYTD]


    Does that make sense? Check these images:

    Thanks,
    Mahboob Mohammed


    Snap 01.png (29.5 kB)
    Snap 02.png (24.6 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12, 2016 at 05:16 AM

    Option 2:

    Create a new variable with formula and this is it.

    =If (Year(CurrentDate())=[Year C] And [Month C]=MonthNumberOfYear(CurrentDate())) Then FormatDate(CurrentDate();"mmmm") + " (MTD)" Else FormatDate(ToDate([Month C];"MM");"mmmm")

    TIP: You can add the month number to the cross tab and then hide it to play around with the sorting.


    1.jpg (36.8 kB)
    2.jpg (54.3 kB)
    3.jpg (138.8 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I thought this fixed my requirement but I had an issue today.

      When the user selects multiple years in user prompt then I get [multiple value error] while displaying the current month name.

      I am expecting 'May MTD' for current month.

      This logic works ok when user selects only 1 year in prompt.

      User has to select multiple year because on the same report I have another trend line chart for few years.

  • avatar image
    Former Member
    May 26, 2016 at 12:36 AM

    I thought this fixed my requirement but I had an issue today.

    When the user selects multiple years in user prompt then I get [multiple value error] while displaying the current month name.

    I am expecting 'May MTD' for current month.

    This logic works ok when user selects only 1 year in prompt.

    User has to select multiple year because on the same report I have another trend line chart for few years.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Vishal,

      Create variable to get month year number in format MMYYYY

      and use this variable along with the month name field and hide it.

      For multiple years the month name will repeat but not the month year number. so you will not get multi value error

      =[Year]*100+ToNumber(FormatNumber([Monthnumber];"##"))

      this will give values like 201505 and 201405

      and corresponding to this you will have May(MTD) and May

      One more point is if you are showing data for multiple years then you should be showing Month and Year in the crosstab rather than month alone. April 2014 May 2014 ..April 2015 May(MTD) etc