MUN Calculation Error

[cognos05]

Hi,

I was using this mun calculation in my slicer for days dimensions

#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) + ']' #

I got an error today as my mun was
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[1] and not 01
for days from 10 to 31 it will work fine.

How do I check this on days from 1 to 9.
should I do if previous day is 1 to 9 then then show 'd' or 'dd'.

Thanks,
Nithya

[cognos05]

I used this

if(#timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) #>9)
Then
(#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) + ']' #)
Else
(#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask(  _add_days ($current_timestamp, -1 )  , 'd' ) + ']' #)

since it is stiill having the condition it say 01 member cant be formed.

So I think the way would be add last member of days dimension on the slicer without creating MUN in SLicer.

Thanks,
Nithya

[schrotty]

Don't misprize the power of macro:

Code Select Expand


#substitute('^0', '', timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) ) #


replaces the '0' at the beginning of a String with ''  :)

[cognos05]

This is expression what I currently have

#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) + ']' #

Where to I replace the string and how to check for If condition.

I cant use both 'dd' or 'd' in my expression since it is dynamic right.

How should my expression for slicer be, so it picks if its 1 or 10.

Thanks,
Nithya

[cognos05]

So I have this ,
so example day 1 the mun is
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[1]

on Day 10 the mun will be
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[10]

so will the below condition work for both 
substitute ( pattern_string, replacement_string, string_expression|array_expression )
Searches for a pattern in a string or in the string elements of an array and substitutes the first occurrence of "pattern_string" with "replacement_string".
for 01 to 09 it would replace 0 with empty so it becomes 1 to 9
but say if it 10 , its still going to replace 10 ' zero as empty and make it 1 .

#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +substitute('^0', '', timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) ) + ']' #

so how should my slicer be adjusting to all values (if and else)

[Lynn]

So I have this ,
so example day 1 the mun is
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[1]

on Day 10 the mun will be
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[10]

so will the below condition work for both 
substitute ( pattern_string, replacement_string, string_expression|array_expression )
Searches for a pattern in a string or in the string elements of an array and substitutes the first occurrence of "pattern_string" with "replacement_string".
for 01 to 09 it would replace 0 with empty so it becomes 1 to 9
but say if it 10 , its still going to replace 10 ' zero as empty and make it 1 .

#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +substitute('^0', '', timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) ) + ']' #

so how should my slicer be adjusting to all values (if and else)

Can't you easily test this to find out? Today is the 5th so you should be able to find out how it works for a date with a single day. You can change the -1 argument of your add_days function to get to the 10th or any other day you like.

However, if I'm not mistaken the carat ^ symbol at the start of the pattern string anchors the zero to the beginning of the string so it will match 01, 02, etc because they begin with zero. It will not match 10, 20, or 30 because those strings end, rather than start, with zero.

I think pattern strings in Cognos macros follow the normal rules for regular expressions so that ^ is start of a string and $ would anchor to the end of a string. Of course there are many more aspects to regular expressions which are very powerful but I'd have to channel back further to my Unix era to remember anything more about them.

[cognos05]

Yes Lynn you are correct , it checks for only string that starts with 0 . I though I will not be able to test since I dont have members after 5 , yes I did subtracting -6 and for 30 it showed 30 and dint replace as 3.

Thanks,
Nithya

[cognos05]

So this was the condition I was using to check if the previos day member exists in the cube and then show hide my report

if(cast(caption(tail (children([DailySales].[Day].[Day].[Day]->:[PC].[@MEMBER].[Day]),1)),integer) =#timestampMask(  _add_days ($current_timestamp, -1 )  , 'dd' ) #)
Then(1)
Else(0)


Yes it was working when using caption of last member and comparing with previous day .

but the last member is not always previous today. so today it had also day 5 sales and day 4 sales. but the report should run only for 4 .

so the above else failed and it said no sales.

so what would be the suggestion for this scenario. I want to it validate it in report studio itself.

Thanks,
Nithya