Modify/Manipulate Date MUN with Data item

[oscarca]

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.

[CognosPaul]

What is the expression behind last sales date?

[oscarca]

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)

[CognosPaul]

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.

[oscarca]

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))

[CognosPaul]

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:

Code Select Expand

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)


[oscarca]

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

[oscarca]

It worked splendid(Y).

Cheers Paul !!!

[Andrei I]

I think this should work as well (a little simpler):

Code Select Expand

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  )