add last years period

[EBee]

@CUR_REV
-----------------------------
if {MB_View.Ad_Type} = 'C' and
{MB_VIEW.Period} = {?Period} then
{MB_View.Insert_Cost} - {MB_View.Insert_Discount}
---------------------------------------------

{MB_VIEW.Period} 1,2,3. . periods -- sample data
{MB_VIEW.Insert_Date} 1/26/2001 -- sample data

Revenue
Current Period
Last year's current period
variance


I need some help to figure how I can calculate the revenue of last years same period. I figured that I need to add some date parameter in my @CUR_REV formula but not sure where to put it

thanks
ebee

 

[synapsevampire]

Assuming {?Period} is a range date.

And I'm guessing that you're using the {?Period} in the record selection formula.

I'd create 2 additional formulas:

PrevYearStart:
datediff("yyyy",-1, minimum({?Period}))

PrevYearEnd
datediff("yyyy",-1, maximum({?Period}))

Now you can also use these to filter rows in the record selection formula, and in whatever formulas required to qualify the data.

So you'd have something like this in the record selection formula:

({MB_View.Ad_Type} = 'C' )
and
(
({MB_VIEW.Period} = {?Period})
or
({MB_VIEW.Period} >= {@PrevYearStart}
and
{MB_VIEW.Period} <= {@PrevYearEnd})
)

This will limit the rows returned to CR to the period specified, plus the previous year same period, for those that have ad_type = 'C'

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[EBee]

actually, my period is not a date range. It is period 1,2,3 ets. but I guess I need to associate the date as well since I need to read each record that have the previous year.

this is what I have so far

if Year({MB_VIEW.Insert_Date}) = year
MB_VIEW.Insert_Date} - 1 ) and
{MB_VIEW.Period} = 1 then
{MB_VIEW.Insert_Cost} - {MB_VIEW.Insert_Discount}


I just put one for now so i can see all records for period 1, but it is a prameter field ( number field)


what do you suggest??

thanks
erwin

 

[synapsevampire]

I still have no idea what a period is, is it a quarter, month, something else?

For both years, the record selection would be something like:

(
Year({MB_VIEW.Insert_Date}) = year(currentdate)
or
year({MB_VIEW.Insert_Date}) = year(currentdate)-1
)
and
{MB_VIEW.Period} = 1

You formulas would have the year and period your aggregating and whatever other criteria makes sense.

The record selection criteria is found at:

Report->Edit Selection Formula->Record

The problem with the YEAR approach is that this won't get passed to the database, so performance will be bad.

If you build the dates for the Period, and use those to compare against the date field, you'll get much better performance.

Here's a sample of how to obtain the current quarter:

DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

Please describe your data types and expected output if this hasn't resolved.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com