topic Average Daily Charge Most Recent 6 months in Visualization and Usability

Average Daily Charge Most Recent 6 months

akuttler — Fri, 11 May 2018 20:12:52 GMT

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)?

Re: Average Daily Charge Most Recent 6 months

jwjackso — Fri, 11 May 2018 20:43:22 GMT

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)

Re: Average Daily Charge Most Recent 6 months

zebhashmi — Fri, 11 May 2018 20:49:46 GMT

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

Re: Average Daily Charge Most Recent 6 months

akuttler — Mon, 14 May 2018 16:51:56 GMT

I tried this expression and its giving me NULL

Re: Average Daily Charge Most Recent 6 months

joseduque — Mon, 14 May 2018 17:01:38 GMT

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?

Re: Average Daily Charge Most Recent 6 months

akuttler — Mon, 14 May 2018 17:03:53 GMT

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