Create parallel date periods with data items on chart

[b737]

Hi,
I am working with DMR, and I have date dimension. I need to compare period from 01.04.2018 till 31.03.2019. with the same period 01.04.2017. till 31.03.2018. I know to create filter, but the problem is that I need two physical data item which I can put on chart, crosstab, and compare it

dataItem1 - 01.04.2018 till 31.03.2019
dataItem2 - 01.04.2017 till 31.03.2018

Any help?

Thanks

[CognosPaul]

Is this a fiscal year? Do you have a fiscal year time dimension? If so it should be as easy as doing [2019] / [2018].

If it's something that you're building on the fly you should use the member function.
Data Item1:
member(total(currentMeasure within set filter(<FILTER>),'2019','2019',[Time Dim Hierarchy])

Data Item2:
member(total(currentMeasure within set filter(<FILTER>),'2018','2018',[Time Dim Hierarchy])

That would then behave like any other member from the hierarchy, and you can use them in sets, tuples, or standalone calculations.

[b737]

Thank you. It is not fiscal year, I have all the time this kind of requirements, with different dates.

Can you please write me whole function, because when I write

member(total(currentMeasure within set filter ([Period].[Date].[Date_ID], [Period].[Date].[Date_ID] between to_date('01.03.2017','dd.mm.yyyy') and to_date('31.03.2018','dd.mm.yyyy'))

I  got error that to_date function is supported in this kind of usage

[CognosPaul]

The 3 part path that you wrote looks like it's a relational data item, not a dimensional which would be 4 or 5 parts.

In your DMR model make sure that the date dimension has a date attribute dragged in as a property of the date level. This needs to be either a date datatype or an integer set up as YYYYMMDD.

In the report the data item should be referencing the level and then the attribute you dragged in.

Examples:

filter([Namespace].[Time Dimension].[Date Hierarchy].[Date Level],[Namespace].[Time Dimension].[Date Hierarchy].[Date Level].[DateAttribute] between '2017-03-01' and '2018-03-31')
filter([Namespace].[Time Dimension].[Date Hierarchy].[Date Level],[Namespace].[Time Dimension].[Date Hierarchy].[Date Level].[DateNumber] between 20170301 and 20180331)

I recommend putting that into a new query and testing with view tabular data. The expected result would be a set of dates inclusive.

Once you get that, you can convert that to a calculated member by wrapping it in member(total(..))

[b737]

I am using to_date because date is stored like [2019-01-01 00:00:00.000]. Anyway, when I try like you said I am getting error

Invalid coericion from 'member' to 'value'

Thank you verrryyy much for help.

[CognosPaul]

To confirm, you have the date set as an attribute on the day level in the time dimension?

Take a look at the attached screenshot of my model. With it set like that I can use:

Code Select Expand

filter([Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date],
[Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date].[Date] between 2019-01-01 and 2019-03-31)

or

Code Select Expand

filter([Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date],
[Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date].[Date] between OCTOTHORPprompt('begin date','date')OCTOTHORP and OCTOTHORPprompt('end date','date')OCTOTHORP)

(replace the octothorps with the correct symbol, as Cognoise blocks my posts when I use them)

[b737]

Yes, my time dimension is similar, I have attached picture, I have translated names.

With this second code I have succeeded with function filter, it works. But when I put that in this expression

Data Item1:
member(total(currentMeasure within set filter(<FILTER>),'2019','2019',[Time Dim Hierarchy]) 

I got error that Cognos can not decompose query.

Thank you.

[CognosPaul]

Can you post the entire member expression?

It should look like

Code Select Expand

member(total(currentMeasure within set filter([Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date],[Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date].[Date] between 2019-01-01 and 2019-03-31),'2019','2019',[Dimensional Layer].[Bill Date Dimension].[Date Hierarchy])


Try putting that into an empty query with a couple of measures. When you view tabular you should get one row with the calculated member and the measures for the selected date range. Next remove the calculated member as a data item from the query and put it directly into the slicer. It should behave exactly the same.

Can you post the full error? There might be some hints as to where the problem is coming from. But a few shot in the dark guesses.

1. Do you have any detail filters?
2. Are you mixing relational and dimensional logic or functions? (case when, if then, substring)
3. Are you pulling in any relational items into the query?

[b737]

Thank you very much.

I have started part by part to see what is going on with expression, like you said. After filter was working fine, I add Total and get results what I have expected. So this expression has done work for me ;)

Code Select Expand

total(currentMeasure within set filter([Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date],[Dimensional Layer].[Bill Date Dimension].[Date Hierarchy].[Date].[Date] between 2019-01-01 and 2019-03-31)

I will try to find what was the error in  function member with this expression. 

Thank you very much, once more.