last 12 months count

[#1]

Hi,

Can anyone please help me with simple requirement. I need to show last 12 months count of employees in a pivot table..I do have a date column and the latest month in the database is not the current month..i have tried to use

=count(if(date(floor(datefield))>=$(v11Months) and date(floor(datefield)) <= $(vEnddate),employee))

I'm having timestamp in the date filed so I used floor function to remove timestamp..

where v11months=date(floor(AddMonths(max(datefield),-11)))

and enddate=date(floor(max(OBGRADEAPPROVED),-11))

the solution might be simple and I might be doing in the wrong way..

any help is highly appreciated..

[jagan · Accepted solution]

Hi,

Try like this

=count({<DateField={'>=$(=MonthStart(Max(DateField), -11))<=$(=Max(DateField))'}>} Employee)

Hope this helps you.

Regards,

Jagan.

[#3]

no luck it is always showing total count..

[jagan]

Hi,

Can you post sample file.

Regards,

Jagan.

[CELAMBARASAN]

Check with this..

Count({<DateField={'>=$(=TimeStamp(MonthStart(Max(DateField), -11)))<=$(=Max(DateField))'}>} Employee)

[#6]

my bad it was working..if I was selecting a time period,but what if I want to show the data from the past 12 months from the latest month always..

[jagan]

Hi,

If you have any other date filters like Month, Year, Quarter then use like thi

=count({<YearField=, MonthField=, QuarterField=, WeekField=, DateField={'>=$(=MonthStart(Max(DateField), -11))<=$(=Max(DateField))'}>} Employee)

Hope this helps you.

Regards,

Jagan.