Year and Month fields

building semantic layer
1

Hi All,
I am trying to set up a Filter for rolling 12 months Data. However I can’t find a proper calendar Date object in the universe.
What I have is a Year numeric field and a Month Numeric field. I concatenated them to make a “yyyymm” field but it is not a solution.
The Year data is :
Year

2012
2013
2104

Month

01
02
03
04
to
12

Is there a way to use these 2 numeric fields to create a Date object by which I can filter for last 12 months Data?
I need Data monthly wise.so each time Report runs, we want to see the previous 12 months,
Need Help!!

:crazy_face:

americanmc :hong_kong: (BOB member since 2009-12-31)

2

Try this

I hope you are using Oracle database. Syntax may change based on your database.

create a filter condition in the designer/IDT with the below formula.

=to_number(<Year_month_concat_Object>)
between
concat ( extract(year from add_months(sysdate,-12)),extract(month from add_months(sysdate,-12)))
and
concat ( extract(year from sysdate),extract(month from sysdate))

and use this for rolling 12 months.

sumanreddy (BOB member since 2008-08-05)

3

[Moderator Note: Moved from WebIntelligence XI to Semantic Layer / Universe Designer]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

Thanks for your help Reddy,
I tried your formula but in IDT, it is giving error. Seems like extract(year from …is not supported. Its giving error.
I am looking for an alternative way to extract year and month from Sysdate.
I am not sure how trunk(sysdate) will work, any ideas??
Thanks for help!!

americanmc :hong_kong: (BOB member since 2009-12-31)

5

you could just use sysdate-365

erik.stenson :us: (BOB member since 2012-07-30)

6

Now the formula is working using the same one given by Reddy, however, it is returning year and month from 201401 to 201412.
Here is filter object sql seen in webi:

  (    ( YEAR||MONTH )
 between 
 concat(extract(year from add_months(sysdate,-12)) ,extract(month from add_months(sysdate,-12))) 
 and 
 concat(extract(year from add_months(sysdate,-1)) ,extract(month from add_months(sysdate,-1)))
  
  )

The result is coming as 2014 with all months. Nothing from 2013. Need help on this, thanks.

americanmc :hong_kong: (BOB member since 2009-12-31)

7

Try this:

( ( YEAR||MONTH )
between
concat(extract(year from add_months(sysdate,-12)) ,extract(month from add_months(sysdate,-1)))
and
concat(extract(year from add_months(sysdate,-1)) ,extract(month from add_months(sysdate,-1)))

)

8

:blue:
Unfortunately, it didnt work and still getting same result. Getting 12 months back, from 201401 to 201412.

Any ideas?? :hb:

americanmc :hong_kong: (BOB member since 2009-12-31)

9

Hang on, think I’ve figured something out. Don’t usually need to use extract but I think there was a flaw in that logic anyway.

Try this instead:
( ( YEAR||MONTH )
between
concat(extract(year from add_months(sysdate,-13)) ,extract(month from add_months(sysdate,-13)))
and
concat(extract(year from add_months(sysdate,-1)) ,extract(month from add_months(sysdate,-1)))

)

Essentially it’s the year month from 13 months ago to the year month from last month, which I believe is what you want :slight_smile:

10

Thanks,
I’ve tried again and it is giving same result.
It shows:
2014 01
2014 02
until
2014 12

I noticed that even if I make the syntax add_months (sysdate,-15), it still gives only 12 rows back with the year 2014.
So I think it is reading the 12 unique values in the month object and returning them.
The webi sql is given here:

SELECT
  Table.YEAR,
  Table.MONTH
FROM
Table
WHERE
  (    ( Table.YEAR||Table.MONTH )
 between 
 concat(extract(year from add_months(sysdate,-13)) ,extract(month from add_months(sysdate,-13))) 
 and 
 concat(extract(year from add_months(sysdate,-1)) ,extract(month from add_months(sysdate,-1)))
  
  )

I have given up and resorted to using one of the Date fields in the table, although it is not best choice but going with it, using following syntax:

table.Date between trunc(add_months(sysdate,-12),'MM') AND last_day(trunc(sysdate,'MM')-1)

THANKS ALL FOR THE HELP.
:wave:

americanmc :hong_kong: (BOB member since 2009-12-31)

11

Is extract(month from …) Oracle syntax or are you using IDT syntax?

If you’re using Oracle, I’d have used something like:

Year||Month between to_char(add_months(sysdate,-13),‘YYYYMM’) and to_char(add_months(sysdate,-1),‘YYYYMM’)