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,
Any macros expert , please help me with this .
Thanks,
I dont think you can use CAST within the macro, but you can use it outside.
example: cast(#$current_timestamp#;date)
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]
Guys any help on this , I tried using tolocal inside lastofmonth macro but dint work.
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:
#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:
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.
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,
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 .
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.
Thank you so much , it worked !!! I really appreciate your help!!