Average Daily Charge Most Recent 6 months

[akuttler]

Hello,

The following expression gives me average daily charges:

(Sum({<ITEMTYPE={'C'}>}Trans)) / (Count(Distinct  ([Transaction Date])))

How would I re-write it to give me average daily Charges only for the most current 6 months (transaction date)?

[jwjackso]

Try

(Sum({<ITEMTYPE='{'C'},[Transaction Date]={">=$(=Date(AddMonths(Now(),-6)'DD/MM/YYYY'))"}>}Trans)/

Count({<[Transaction Date]={">=$(=Date(AddMonths(Now(),-6)'DD/MM/YYYY'))"}>} Distinct [Transaction Date]))

You will need to adjust the Date format to your date.  Check out calendars if you don't use them (Creating A Master Calendar)

[zebhashmi]

Or

if ([Transaction Date]>AddMonths([Date],-6),(Sum({<ITEMTYPE={'C'}>}Trans)) / (Count(Distinct  ([Transaction Date]))))

[akuttler]

I tried this expression and its giving me NULL

[joseduque]

Set Analysis is your best choice,  but just to make sure  you need the average for each daily separately or could be a sum of the 6 months transaction divide to 6 months transaction date?

[akuttler]

I just need the sum of 6 months divided by 6 months of transactions dates.