MUN Issues ,need to cast the timestamp when using lastmonth

[cognos05]

Hi ,

I have a member unique name which is for Feb 2017 as below

[SalesCube].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[20170201-20170228]

I am generating this mun using below macro

#'[SalesCube].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#


This works fine at some times, but at times this throws me an error since
The _last_of_months() function converts an input TIMESTAMP bearing TIME ZONE to a UTC value before calculating the last of month.

To avoid the conversion of a TIMESTAMP bearing TIME ZONE to a UTC value, you can cast the TIMESTAMP bearing TIME ZONE to a TIMESTAMP through a function like CAST(<value> AS TIMESTAMP).

How do I use cast inside my last of month function , in my below expression .

#'[SalesCube].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month(Cast($current_timestamp AS TIMESTAMP)),'yyyymmdd') + ']'# is throwing an error .

Please help .

Thanks,

[cognos05]

Any macros expert , please help me with this .

Thanks,

[Deep750]

I dont think you can use CAST within the macro, but you can use it outside.

example: cast(#$current_timestamp#;date)

[cognos05]

then how do I achieve creating dynamic mun without using last month .

I need a macro expression for the same to be generated without using last month function as it gives utc timestamp for conversion.

[SalesCube].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[20170201-20170228]

[cognos05]

Guys any help on this , I tried using tolocal inside lastofmonth macro but dint work.

[CognosPaul]

Cast definitely won't work as it's not a macro function.

I've never had an issue with _first_of_month or _last_of_month converting to UTC before, can you create a new query with:

Code Select Expand

#sq(_last_of_month($current_timestamp))#

and paste in the results?

Does this issue happen after or before a certain time of day?

If it is related to the time of day (which is possible if the time zone is screwing things around), try this:

Code Select Expand

timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd')

The inner timestamp mask essentially truncates the datetime, hopefully allowing the _last_of_month function to work as expected.

[cognos05]

Sorry if  i had not stated the issue clearly .
MY Mun :
[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[20170201-20170228]

#'[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].['+timestampMask(_first_of_month(_add_days ($current_timestamp,-1)),'yyyymmdd')+ '-' + timestampMask(_last_of_month(_add_days (toLocal ($current_timestamp),-1)),'yyyymmdd') + ']'#

so what happens is , issue is occuring only between 8pm and 11:59 pm .

Result I am getting is [Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[20170201-20170227] which is not a member and this is because last of month converts the value of time stamp to UTC which is 5 hours ahead , this is explaine din below document .

http://www-01.ibm.com/support/docview.wss?uid=swg21656999

so obviously, i should not allow lastmonth macro to do this conversion.

I will try your code ,

Thanks,

[cognos05]

And what does this do

If it is related to the time of day (which is possible if the time zone is screwing things around), try this:
Code: [Select]
timestampMask(_last_of_month(timestampMask($current_timestamp,'yyyy-mm-dd')+'T00:00:00Z'),'yyyymmdd')

The inner timestamp mask essentially truncates the datetime, hopefully allowing the _last_of_month function to work as expected.

I just want lastofmonth to take my current timestamp instead of taking utc ,

what does + 'T00:00:00Z' do , I can test only after 8 pm .

[CognosPaul]

The T00:00:00Z is shorthand for " 00:00:00+00:00". The T replaces a space in a timestamp, and the Z replaces the time zone +00:00:00. It should ensure that the current_timestamp isn't affected by your time zone.

[cognos05]

Thank you so much , it worked !!! I really appreciate your help!!