This discussion is archived
14 Replies Latest reply: Oct 29, 2013 12:30 PM by OracleUsrName

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

Currently Being Moderated

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
Currently Being Moderated

use MSUM function and set 3

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

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
Currently Being Moderated

The 3rd column in your post.

or else

msum(count(date),3)

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

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
Currently Being Moderated

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
Currently Being Moderated

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
Currently Being Moderated

Have you tried timestamp?

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

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
Currently Being Moderated

Sunil,

Would you be kind enough to share your doc?

thx

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

Sunil,

Please share the doc when you get a chance.

Thank you.

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

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

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

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
Currently Being Moderated

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

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

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

#### Legend

• Correct Answers - 10 points