Hello Cognoise,
I have a Data item (Last sales date) that returns a date "2019-12-03". Is it possible to use this Data item to create a new MUN for the Time dimension.
This is how a date member looks like.
[Cube].[Time].[Year-Month-Day].[Day]->:[M14].[[Time]].[Year-Month-Day]].[Day]].&[2000-02-09]]]
I would like to modify the MUN like this: [Cube].[Time].[Year-Month-Day].[Day]->:[M14].[[Time]].[Year-Month-Day]].[Day]].&['+[Last sales date]+']]].
It doent seem to generate any date but no error occurs.
Anyone here with experience in manipulating MUN's ? All tips and solutions are greatfully accepted.
What is the expression behind last sales date?
Hey Paul!
It is:
item(tail(filter(members([Cube].[Time].[Year-Month-Day].[Day]), tuple([Invoice Amount LOC], currentMember([Cube].[Time].[Year-Month-Day])) <> 0), 1), 0)
You should be able to use the results of that - item returns a single member.
Try creating a crosstab that's set up with measures in the columns, and the Last sales date in the rows and then drag the correct date member from the dimension below. I don't see any reason they wouldn't be the same.
The reason why I try to do this Paul is because cognos didnt allow me to place [last sales date check] data item in the slicer filter which is the main purpose. It complains saying A value expression may not be used in a slicer, unless wrapped in a Member function. So thats why I tried to manipulate the MUN instead.
[last sales date check] =
cast(case
when(cast([closingPeriod],date) = current_date)
then(_add_days(cast([closingPeriod],date),-1))
else(cast([closingPeriod];date))
end;varchar(10))
The problem you're having with [last sales date check] is it's using relational expressions. Cognos will have to process the query, store the results in a cache, and process off that. It's important to remember is that the date member isn't a date, it's a pointer to an object in the cube that looks like a date.
It looks like you're trying to ensure that the last sales date is never today. There are a few ways to handle that.
Let's try this:
item(tail(
filter(
except(members([Cube].[Time].[Year-Month-Day].[Day])
, #'[Cube].[Time].[Year-Month-Day].[Day]->:[M14].[[Time]].[Year-Month-Day]].[Day]].&['+timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#
), tuple([Invoice Amount LOC], currentMember([Cube].[Time].[Year-Month-Day])) <> 0), 1), 0)
Hello Paul,
That makes a lot of sense. I must say this looks like a much cleaner and efficient solution. I reply back when I have tested it.
Best regards,
Oscar
It worked splendid(Y).
Cheers Paul !!!
I think this should work as well (a little simpler):
item( tail(
filter(
except(
[Cube].[Time].[Year-Month-Day].[Day]
, #'[Cube].[Time].[Year-Month-Day].[Day]->:[M14].[[Time]].[Year-Month-Day]].[Day]].&['+timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#
)
, [Invoice Amount LOC] <> 0
)
), 0 )