cancel
Showing results for 
Search instead for 
Did you mean: 

Current Report Month & Year calculation?

Former Member
0 Kudos

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]

Accepted Solutions (1)

Accepted Solutions (1)

mhmohammed
Active Contributor
0 Kudos

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

Answers (2)

Answers (2)

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

former_member207052
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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.