finding average earning over period of employment

It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

find the average monthly earning while you have been an affiliate

affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

spavgmonthlyearning affiliate_id

any help would be greatly appreciate even a point in the right direction would be fantastic.so this will do it.
is this pure sql or will it only run on sqlserver
thank you for your help

DECLARE @.maxdate as datetime
DECLARE @.mindate as datetime
set @.maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
set @.mindate = (select min(trans_dt) from dbo.affiliate_log)
select sum(earning)/datediff(M,@.mindate,@.maxdate)
from dbo.affiliate_log where affiliate_id = 1|||select DATEPART(m,date_field), avg(sales)
from table
group by DATEPART(m, date_field)|||It depends on what you mean by "average".

The simple average monthly earning would just be the total earnings divided by the total months.

select affiliate_id,
sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
from yourtable
group by affiliate_id

Or if they skip some months:

select affiliate_id,
sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
from yourtable
group by affiliate_id

Financial Periods YTD & Date Ranges

I am developing a set of reports for a site based on AS2000.

The reports will have columns for each finance period in the current year from period 01 to the current reporting period from the Primary Time dimension.

i.e. If period 4 is the default period, The report should show columns for periods 1,2,3,4 with a total that sums the four columns.

When the default period 5 the columns should be 1,2,3,4,5.

I cannot use the current year and explode that to show all periods in the year, as there are postings in future periods beyond the current accounting period that should not be displayed on the report.

I am setting the default member of the time dimension to the current reporting period. (2005/08 for example)
I am trying to use a named set based on an MDX expression to set the column range for the report.

The first test I tried was to set the MDX for the period range to some fixed values
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].&[200508]}
This gave the expected result showing columns 01/2005 to 08/2005

I then tried changing the last period to use the default member of the dimension
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].DefaultMember}
This again gave the expected result range and I could change the default member on the dimension and see the columns on the report update correctly.

I then tried testing that the first period can be found correctly
{[Primary Time].[ACCSCAL].&[200508].FirstSibling:[Primary Time].[ACCSCAL].&[200508]}
This again gave the expected result range from 01/2005 to 08/2005

I then tried combining the default member and first sibling calculations
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling:[Primary Time].[ACCSCAL].DefaultMember}
This gave a single column "All Primary Time", this was not what I was expecting.

So I tried changing the mdx to test the value of the first sibling of the default member, expecting 01/2005
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling}
This again gave a single column "All Primary Time".

So I then tried changing the mdx to test that a single period would work in the named set
{[Primary Time].[ACCSCAL].DefaultMember}
This again gave a single column "All Primary Time".

So I tried changing the mdx to test the value of the default member, expecting 08/2005
{[Primary Time].[ACCSCAL].&[200508]}
This again gave a single column for 08/2005 as expected

By now this has me totally confused!

Do you have any idea why the default member works when used as the second part of the range but does not work when used on its own or as the first element of the range?

Is this the correct way of achieving the report I am aiming for or is there a better method to use?

Any suggestions you have would be welcome.

Apologies is this is a FAQ.

What is the actual MDX expression for the Default Member of [Primary Time].[ACCSCAL]?|||The Default Member is [Primary Time].[ACCSCAL].&[200508]

Filtering without a tuple-set ?

a normal Filter with a tuple-set:

Filter(
[Brand].members,
(([Measures].[Sale],
[Posting Period].[Year].&[2005]) > 0)
)

The brands where we sold something in the year 2005. This works fine but....

a Filter within a range of days ? :

Filter(
[Brand].members,
(([Measures].[Sale],
ClosingPeriod([Posting Period].[Day]).lag(365):ClosingPeriod([Posting Period].[Day]))) > 0)

The brands where we sold something in a range between currentday -365 days and currentday.
This won't work with the filter-function ! Is there an other way to do this ?

Kind regards

If your Sales measure does not contain negatives you could simply aggregate the measure for the range of dates.

Filter(
[Brand].members,
Aggregate(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ) > 0)

If your measure might contain negative figures and there is a chance that a date range could sum to 0 even though there are sales for the specified period, you might be better off getting a count of the non empty cells.

Filter(
[Brand].members,
NonEmpty(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ).Count > 0)