How to Retrieve Quarter from Year and Month

building reports webintelligence
1

Hi,

I have Year and Month columns are there in my data base. I want to calculate Quarter wise revenue. How can i retrieve Quarter by using Year and Month Columns. Any work around this please…

Thanks in advance

vijayakrishna.bos (BOB member since 2007-08-29)

2

Does “Automatic Time Hierarchy” applicable for this?

jvmauricio :philippines: (BOB member since 2009-08-03)

3

We have to create Manually. We are using SQL Server.

vijayakrishna.bos (BOB member since 2007-08-29)

4

How about a Derived Table in Universe?

jvmauricio :philippines: (BOB member since 2009-08-03)

5

We are using SAP cubes . So there is no concept of tables. Any thing we can do is in the report level only. Is there any function is there to retrieve quarter out of Year and Month.

vijayakrishna.bos (BOB member since 2007-08-29)

6

Why not modify the BEX query and provide an object “Quarter”?

Andreas :de: (BOB member since 2002-06-20)

7

Try this trick:

Create a column Month Number 1-12. Make Jan as 1 Feb as 2 Mar as 3 and so on.

Now use this condition to develop an object

If Month num In 1,2,3 then qtr 1 else if month num in 4,5,6 str 2 else if month num in 7,8,9 qtr 3 else qtr 4 end if.

Declare this variable global and use this whereever you need.

I hope this logic should work for you.

'coz we create cubes in SSAS for Voyager related work for our client and to handle these month in most robust ways we added this one column (month number) in month table and we did a lot oof magic :idea: with the help of this. :smiley:

Please let me know if this works.

Cheers,

shiva.tomar (BOB member since 2007-10-05)

8

Why can’t you use the Quarter() function from WebI…

=[Year]+" Q"+quarter(todate(([Month]+"/"+[Year]);“MM/yyyy”))

will give you values as 2008 Q1, 2008 Q2 … etc

rimpa :india: (BOB member since 2008-04-14)

9

I guess he want to build up a hierarchy while building the cube wherein he needs the Quarter dimension. So he needs to build his datamodel in such a way that he can handle month, Qtr, Yr.

'coz he posted the same question again and Marek has locked that topic asking him to refer this.

I suggested that way 'coz thats much more faster and if you implement something at database level rather than just at universe or report level it is always more stable and performance oriented. 'coz in real time scenario we need performance. Also from consultation point of view if he has to satisfy 2 sets of users (1. Taking data from universe & 2. Taking data from cube directly) then in that case he has to redesign all that. Which i believe add complexity to his model.

Any corrections to this are most welcome.

Cheers,

shiva.tomar (BOB member since 2007-10-05)