14 Replies Latest reply: Oct 29, 2013 2:30 PM by OracleUsrName

# Calculate Calendar days - last 3 months in each time period

Hello all,

I need to calculate the calendar days for the last 3 months in each of the time period present in the report. Here is an illustration -

 Year Month Totals days in month Totals days in last 3 months 2013 Jan 31 92 Feb 28 90 Mar 31 90 Apr 30 89 May 31 92 Jun 30 91 Jul 31 92 Aug 31 92 Sep 30 92 Oct 31 92 Nov 30 91 Dec 31 92

Can this be done in the analysis or should I create one in the RPD? What is the formula?

Thank you.

• ###### 1. Re: Calculate Calendar days - last 3 months in each time period

use MSUM function and set 3

• ###### 2. Re: Calculate Calendar days - last 3 months in each time period

Srini,

Should I do MSUM(<Month Column>,3)? I'm struggling with the syntax.

Thank you.

• ###### 3. Re: Calculate Calendar days - last 3 months in each time period

The 3rd column in your post.

or else

msum(count(date),3)

• ###### 4. Re: Calculate Calendar days - last 3 months in each time period

That did not give the correct value as not all dates were used in the report. I created a formula that seems to give the correct value but when I use it in a pivot it disappears!

timestampdiff(SQL_TSI_DAY, timestampadd(SQL_TSI_MONTH, -2, Cast(Trim(CAST(CAST(trim(Right("Time"."Year Month",2))as INTEGER) as CHAR))||'/1/'||(Left("Time"."Fiscal Period ID",4)) as Date)), timestampadd(SQL_TSI_MONTH, 1, Cast(Trim(CAST(CAST(trim(Right("Time"."Year Month",2))as INTEGER) as CHAR))||'/1/'||(Left("Time"."Year Month",4)) as Date)))

• ###### 5. Re: Calculate Calendar days - last 3 months in each time period

Hi

use Time series function (PeriodRolling) function in .rpd and

in answers apply filter on year column.

you will get result.

Regards

Sunil

• ###### 6. Re: Calculate Calendar days - last 3 months in each time period

Sunil,

The report may be generated across years. For example if the prompt chosen in Feb 2013, the report would have to calculate using Dec 2012 days as well. Filtering on the year will not help here unless I understand you wrong.

• ###### 7. Re: Calculate Calendar days - last 3 months in each time period

Have you tried timestamp?

• ###### 8. Re: Calculate Calendar days - last 3 months in each time period

OracleUsrName,

i worked on this scenario and i got the result. if u want i can share that doc.

Thank You

Sunil

• ###### 9. Re: Calculate Calendar days - last 3 months in each time period

Sunil,

Would you be kind enough to share your doc?

thx

• ###### 10. Re: Calculate Calendar days - last 3 months in each time period

Sunil,

Please share the doc when you get a chance.

Thank you.

• ###### 11. Re: Calculate Calendar days - last 3 months in each time period

Send me u r Email ID. i will forward it to u.

• ###### 12. Re: Calculate Calendar days - last 3 months in each time period

I don't know how to send you a private message. My email is available in my profile.

• ###### 13. Re: Calculate Calendar days - last 3 months in each time period

send a test mail to my email id: sunilkumar.bandla@yahoo.com

• ###### 14. Re: Calculate Calendar days - last 3 months in each time period

Revisiting this issue. The calc makes the report take a long time to run - about 2 mins. How can I make it run quicker?