Need to rollup data for Monthly and Weekly

building reports webintelligence
1

Requirements are:

Sum all counts of [PCMlog_Monthly].[End_Date] for Last Month, Last 2 Months, Last 3 Months, Last 4 Months based on currentdate.

Data is sourced from Oracle 10g table and End_Date has data type date and formatted dd/mm/yyyy.

How do I create the variables for Last Month, Last 2 Months, Last 3 Months and Last 4 Months based on current date?

Then, how do I sum the monthly counts?

This was how PCMlog_YesterdayCount was calculated and worked:
=Sum(If([PCMLog_Daily].[End_Date] = [Yesterday];1;0)) where Yesterday was defined in Designer as TRUNC(SYSDATE - 1).

Your assistance is much appreciated.

Thanks.
Dindin

din8din (BOB member since 2010-06-28)

2

Create Variables as below:

  1. V_LastmonthSrtDate this will give Last months 1st date
=ToDate("01/"+"0"+(MonthNumberOfYear(CurrentDate())-1)+FormatDate(CurrentDate();"/yyyy");"dd/MM/yyyy")

Like wise you can create changing MonthNumberOfYear(CurrentDate())-2 and 3 Last2,Last3 months start dates
2. V_LastmonthEndDate

=LastDayOfMonth([V_LastmonthSrtDate])
  1. Your Sum Variable
=Sum(If([PCMLog_Daily].[End_Date] between([V_LastmonthSrtDate];[V_LastmonthEndDate]);1;0))

Rajubollas :india: (BOB member since 2009-09-04)