Averages

[#1]

I am trying to write an expression that extrapolates a period of data over a full year e.g. (sales /current number of months)x 12. I am struggling to do this so when I select a month the expression automatically changes e.g. August 2012 is selected the calculation would be (August 2012 YTD sales/8)x12.

[#2]

Hi Tom

In what format is your month data stored? If it is a date field, you could use the Month() function to get the number of the month to use in the denominator.

Otherwise, you will need to turn the text field containing the month back into a date using the date#() function, then use the month() around it.

eg =month(date#('August 2012','MMMM YYYY'))*1 returns '8'

Do let me know if this helps,

Erica

[#3]

If u have a date field then use below logic

Sales=Sum(Sales)

current number of months=month(max(Datefield))-month(min(Datefield))

~Kabilan K

[#4]

Hi,

=month(max(Datefield))-month(min(Datefield))   // it won't return correct value for some scenario(Month('01/08/2013')-month('03/21/2012')

So, I have changed the expression like below

current number of months=ceil((MonthName(max(Datefield))-MonthName(min(Datefield)))/30)

~Kabilan K

[#5]

Hi Erica,

Thanks for your help with this, I used - month(date#(Seldate,'MMMM YYYY'))*1which worked until there was no data for the selected month, when this happened no figures were returned. Do you know a way to rectify this issue?

Many thanks,

Tom

[#6]

Not a problem. What expression are you using for the YTD sales? Is this something already stored in a column or are you calculating this via another expression?

Erica

[#7]

Hi Erica,

I am calculating this in the same expression, however I was using sales as an example the actual calculation is as below.

=sum({<SelDate={">=$(=date(yearstart(max(SelDate)),'MMMM YYYY')) <=$(=date(max(SelDate),'MMMM YYYY'))"}, [Fee Group]={ Various fee types }>}[Base Currency Amount (USD)])

Tom

[#8]

And have you tested the bit separately? Does the YTD bit work on it's own?

Just trying to figure out which bit is the issue

Erica

[#9]

I have tested the YTD separatly and it does work on its own.

Tom

[#10]

In which field is the missing data that causes the problem? Is it Seldate itself or in a separate field?

Erica