Filter sales until last day of previous month

[lpoto]

Hello Cognoise Community !

I would like to do a very similar thing to the person who wrote this thread https://www.cognoise.com/index.php?topic=28816.0: I would like to get all sales from the first date of my cube (so no start date needed) until the last day of the previous month :
so I tried this in the detailed filter section of my report:

[MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name] <=_last_of_month (_add_months ( current_date,-1))

But I get several errors (I only have one data item in my report : the sales measure I want to filter):
QE-DEF-0459 CCLException
GEN-ERR-0015 Initially, in date source type(s) ", the function "oneDimension" is not supported
Data source type(s) " - Function "realDataMode" is not supposted
Function "fn:current_date is not supported' in 'OlapQueryProvider'
and so on ....

https://ibb.co/wJMcg93

My date hierarchy looks like this :

https://ibb.co/WsWq4Q1

I don't get it, I have been trying this for days now, and nothing works ! My Date dimension seems pretty basic, I did not develop it because I don't have skills, but I don't get why requesting Cognos to calculate the sales until last month is so difficult ! In a relational model, it is much simplier.

Thank you in advance !
Kind regards

[lpoto]

[UPDATE]
I managed to get rid of the errors BUT I get the wrong results ...

I wrote

Code Select Expand

[Day - Complete Name]<cast(_last_of_month(_add_months(current_date;-1));varchar(12))

instead of

Code Select Expand

[MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name] <=_last_of_month (_add_months ( current_date,-1))

I first inserted the element [Day - Complete Name] in my query. I don't know why it works like this but at least i don't have error messages anymore ...

For info, the data format in the element [Date - Complete Name] is '2020-10-31' ('yyyy-mm-dd'), this is why I have to "cast" to "varchar(12)" (12 caracters in the string).

But the results is completely wrong, it gives me a negative sales number. I checked, it is not the sales we have on or before 2020-10-31, i don't understand...
I created one data item that is equal to cast(_last_of_month(_add_months(current_date;-1));varchar(12)) and it gives 2020-10-31. When I replace in my filter cast(_last_of_month(_add_months(current_date;-1));varchar(12)) by '2020-10-31' it gives me the correct answer.... so why aren't the results equal ?

If anyone has any idea, would help me a lot ! It is quite hard to find as many info and cases on Cognos than excel for instance ! I feel like these forums are the key for Cognos users to develop their skills, so this is why I replied to my own post with a progress I made , but I still can't figure it out ...
Thank you !

[oscarca]

Hello,

The issue I see is that you are trying to filter a member in your Date dimension with a string. A string and a Member are not the same thing (common misconception). Instead you should try to filter the Date with a Date member, so we need to manipulate the MUN of a Date member so it represents last day of previous month.

In my example below I have created a data item that returns current_date member from the Date dimension

Data Item (Named it as current_date) : #'[Sales].[Calendar].[Day].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#

I drag the Newly created data item to the detailed filter:
[Sales].[Calendar].[Day].[Day] = [current_date]

//Oscar

[lpoto]

Hi Oscarca!

First of all, thanks A LOT for your answer!
You are right, I do get a lot confused between members and strings. I tried to create the data item you wrote, but I didn't manage probably because I am not adapting it correctly.

My Date day member looks like this : [MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name]

So could you please confirm I have to translate the data item like this ?

Code Select Expand

[MyCube].[Dim Date].[Dim Date].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]

Without a dynamic setting, my filter usually works like this : [Day - Complete Name]<'2020-10-31' so I thought this meant my Day was actually in a string format ? Actually, the filter works when I convert current date to a string (varchar(12)) as below:

Code Select Expand

[Day - Complete Name]<cast(_last_of_month(_add_months(current_date;-1));varchar(12))

But it gives me incorrect sales number ...

[UPDATE]: the sales number seems correct with the filter right above, when I create a data item that is equal to my sales measure and I set the aggregate function to "Total" instead of "Automatic". I could not change the aggregate function of my "predefined" measure so this is why I tried to recreate another data item that is equal to it .... I don't know if it makes sense !! Could it be that my sales measure was the issue ?  ;D ;D

[oscarca]

You were missing the hashtag sign:
#'[MyCube].[Dim Date].[Dim Date].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#

And you have to make sure that your Date meneber actually have this format yyyy-mm-dd

Aggregation settings could be the reason for the measure to show wrong values yes.

[oscarca]

I also realized that it would be better performance wise to place the newly created data item :#'[Sales].[Calendar].[Day].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'# in the slicer instead of the detailed filter. So we don't need this at all "[Sales].[Calendar].[Day].[Day] = [current_date]" at all.

[lpoto]

Thanks a lot, it works perfectly !!  :D :D
Will save me a lot of time !
Also thanks for explaining the difference between member and string and the trick to insert data item into filter.

Have a good day!

[oscarca]

No worries my friend, glad to help out.

//Oscar

[lpoto]

Sorry to bother you again: may I ask what the "hashtag" sign is doing in Cognos ?
I looked on google but all it says is : cognos on twitter ...  ;D

Thanks!

[bus_pass_man]

The hashtags are symbols which delimitate macros.

" A macro is delimited by a number sign (#) at the beginning and at the end. Everything between the number signs is treated as a macro expression and is executed at run time."

[lpoto]

Great, thanks a lot, I will look for documentation on macros in Cognos to expand my knowledge then.
Thanks again!