topic Re: last 12 months count in QlikView

last 12 months count

Thu, 07 Mar 2013 13:13:50 GMT

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..

Re: last 12 months count

jagan — Thu, 07 Mar 2013 14:22:08 GMT

Hi,

Try like this

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

Hope this helps you.

Regards,

Jagan.

Re: last 12 months count

Thu, 07 Mar 2013 14:52:24 GMT

no luck it is always showing total count..

Re: last 12 months count

jagan — Thu, 07 Mar 2013 14:56:02 GMT

Hi,

Can you post sample file.

Regards,

Jagan.

Re: last 12 months count

CELAMBARASAN — Thu, 07 Mar 2013 15:12:14 GMT

Check with this..

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

Re: last 12 months count

Thu, 07 Mar 2013 15:20:06 GMT

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..

Re: last 12 months count

jagan — Thu, 07 Mar 2013 15:46:40 GMT

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.