Count Function

[Anonymous]

Hi all,

I have a requirement where we need to calculate a value like  (sales/ No of months lapsed).

Ex: if we will select a year 2013 and month='July' then it should calculate the value like sales/6.

So any body can suggest how to calculate the no of months lapsed .

[Anonymous]

We have normal month selection like January,Feburary,...December..
But we need the number format of months for that expression to calculate months lapsed.

[antoniotiman]

If you have Field Date (format mm/dd/yyyy) You can try  :

Sum(Sales)/(Num(Left(Max(Date),2)) -1)

but this don't work if You choose 'Jan'

[its_anandrjs]

For any Month fields which is contains June,July so far but if you try like

=Max(Month) it will gives you 7 and you can also try

=Num(Max(Month)) will will also gives you 7

For your requirement you can try

Sum(Sales) / Max(Month)

Or

Sum(Sales) / Num(Max(Month))

as other members also suggest the same.

[maxgro]

maybe

=max(match(Month,'January','February','March','April','May','June','July','August','September','October','November','December'))

[MK_QSL]

NUM(Date(Date#(Your Month field, 'MMM'),'MMM'))

[Anonymous · Accepted solution]

By using month(date#(MONTH FILELD ,'MMM')) it worked.

Thanks all for ur help.

[#8]

While loading the Month field, add one more field of associated month numbers like field name 'MonthNum'.

Then use the below expression:

sum(Sales)/(max(MonthNum)-1)

Hope this will help you...!

[MK_QSL]

Please close the thread by selecting appropriate answers so that others having similar problems can use.