Yes, I am using DMR, not relational model. The expression is ([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 am using to_date because date is stored like [2019-01-01 00:00:00.000]
The dimension for date is classic time dimension, with year, quarter, month, date
Thank you for help.
------------------------------
Veljko
------------------------------
Original Message:
Sent: Wed April 10, 2019 10:01 AM
From: HENK CAZEMIER
Subject: Create parallel date periods with data items
Could you provide some more details on the expressions that you're using. Are the expressions in terms of DMR or relational? The example that I built was using relational expression to build to measures, which is probably different from the method that you're using.
// Henk
------------------------------
HENK CAZEMIER
Original Message:
Sent: Wed April 10, 2019 03:27 AM
From: Veljko
Subject: Create parallel date periods with data items
Thank you for help, but when I try this I have got error: 'Invalid coercion from 'value' to 'measure'.
Also when try different I got again the same error 'Invalid coercion from 'level' to 'string'.
------------------------------
Veljko
Original Message:
Sent: Tue April 09, 2019 10:31 AM
From: HENK CAZEMIER
Subject: Create parallel date periods with data items
The question:
Based on the Cognos Analytics gosales sample database. Assuming there are relationships between the various tables in this sample database, e.g. ORDER_HEADER – ORDER_DETAILS, ORDER_DETAIL – PRODUCT, PRODUCT – PRODUCT_TYPE, PRODUCT_TYPE – PRODUCT_LINE.
If the measure [Quantity] is defined as:
[gosales_sample].[ORDER_DETAILS].[QUANTITY]
Define [cy quantity] as:
case when
[gosales_sample].[ORDER_HEADER].[ORDER_DATE]
between 2005-04-01 and 2006-03-31
then [gosales_sample].[ORDER_DETAILS].[QUANTITY]
end
And [py quantity] as:
case when
[gosales_sample].[ORDER_HEADER].[ORDER_DATE]
between 2004-04-01 and 2005-03-31
then [gosales_sample].[ORDER_DETAILS].[QUANTITY]
end
Putting the 3 measures in a list report and adding a dimensional level for [PROCUCT_LINE_EN] gives:
QUANTITY cy quantity py quantity PRODUCT_LINE_EN
866234 326012 199094 Camping Equipment
99400 38952 22420 Golf Equipment
301958 143254 20728 Mountaineering Equipment
557854 154568 253020 Outdoor Protection
389908 140642 91884 Personal Accessories
//Henk
------------------------------
HENK CAZEMIER
Original Message:
Sent: Mon April 08, 2019 01:57 PM
From: Veljko
Subject: Create parallel date periods with data items
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 how 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 -31.03.2019
dataItem2: 01.04.2017 -31.03.2018
Any help?
Thanks
------------------------------
Veljko
------------------------------
#CognosAnalyticswithWatson