Monthstart and Monthend

[#1]

Hi All,

Can anyone help me in getting the below calculation,

Count({<Date=Monthstart(Date)>}empid)

As per my requirement i should get the sum(Count of Employees in Monthstart of every Month. and month end of every month)

Regards,

Priyanka

[#2]

Hi Joe,

That was realy helpful.But Monthend Count is not showing properly.Month end count shows total emp id count.

[#3]

Hi Anadh,

That works for monthstart .But Monthend count shows 0 value.Monthend has values.

[its_anandrjs]

Hi,

In Monthend expression write

=Count( if(  trim(Date) = Trim( MonEndDate )   , empid ))

Regards

Anand

[jyothish8807]

Hi Priyanka,

for month end try like this:

if(Date>=monthstart(Date) and Date<=monthend(Date),1,0) as monthendflag.

hope it helps

Regards

KC

[#6]

That would surely flag every row with a 1 would it not?

[#7]

Hi Priyanka,

no problem glad it helped. Not sure why that would be the case, if the month start works. Want to show your working so far and will see if I can help

cheers

Joe

[its_anandrjs · Accepted solution]

Hi Priyanka,

This is your complete script you have the date format issue i thing for this use Trim to remove spaces from field and get the script as

tmp:

LOAD

       Date(Date,'M/D/YYYY') AS Date,

       MonthStart( Trim(Date)) as MonStartDate,

       MonthEnd(  trim( Date)) as MonEndDate,

       empid,

       Type

FROM

mockup.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

N:

LOAD

      Date(Trim(Date)) as Date,

      Date( Trim( MonStartDate )) as MonStartDate,

      Date(Trim( MonEndDate )) as MonEndDate,

      empid,

      Type

Resident tmp;

DROP Table tmp;

And then write expressions:-

Monthstart

=Count( if( Date = MonStartDate  , empid ))

MonthEnd

=Count( if(  (Date) = Trim(MonEndDate)  , empid ))

Regards

Anand

[jyothish8807]

Right, my bad Joe.

[#10]

no worries, just checking it wasn't my brain being funny this early in the morning