In this reports the client is experiencing an issue in Web Intelligence where the Calendar Year/Month shows up in the following format “200,702”

Here’s an example below from Web Intelligence

 

You can test out the Calendar Year/Month by creating a variable “TestCalendarYear”using the IsNumber function

 

This proves we are working with a number; you could also use the following

  • IsDate()
  • IsString()
  • IsLogical()

So now we will have to convert this number to a date, first we will format the number by using FormatNumber() Function

FormatNumber([Calendar Year/Month];”#######”)

Next convert the number to date by using the ToDate() Function

ToDate(FormatNumber([Calendar Year/Month];”#######”); “yyyyMM”)

And then Format the date in the proper format using FormatDate() Function

=FormatDate(ToDate(FormatNumber([Calendar Year/Month];”#######”); “yyyyMM”); “Mmm yyyy”)

 

Here’s what then end result variable looks like

 

 

And the output in Web Intelligence

 

The result we were looking for.

If you have any questions feel free to contact me at info@vynesolutions.com