Skip to Content
May 07, 2020 at 07:50 AM

IDT dimension First Day of month, First Day of Year, etc.



I need to add some Date dimension in IDT (Information Design Tool 4.2 version ©2010 - 2016 SAP SE., Build: 14.2.3.v20170913-2538)

  • First Day of Week
  • FIrst Day of Last Week
  • First Day of Month
  • First Day of Last Month
  • First Day of year
  • First Day of Last Year

This dimension must be used indepent of the rest of dimension.

I try 2 ways:

  1. the first use the function Curdate(). But I cna find first day of last week for example.
  2. The second to create an edit table :

SELECT trunc(sysdate ,'IW') as "First Day of Week",trunc(sysdate,'MM') as "First Day of Month ", trunc(sysdate ,'YYYY') as "First Day of Year",trunc(add_months(sysdate,-1),'MM') "first Day of Month-1", add_months(sysdate,-1) as "Date of Month-1 ",trunc (sysdate-6, 'IW') "First Day of Week-1" , to_date(to_char(sysdate,'YYYY')-1||'/'||to_char(sysdate,'MM/DD'),'YYYY/MM/DD') AS "Year-1",to_date(to_char(sysdate,'YYYY')-1||'/01/01','YYYY/MM/DD') AS "First Day of Last Year"FROM Dual

But when I want add this dimension with other dimension, I have an error in "You cannot run this query because it will produce a Cartesian product(IES 00012)"

I know in UDT, function last_day, add_months exist, but it's not the case in IDT.