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

[ashfaq_haseeb]

Hi,

This must work

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

Count({<Date={'$(=(Monthend(Date)))'}>}empid)

Format your date accordingly.

Regards

ASHFAQ

[PradeepReddy]

try like this...

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

Count({<Date={'$(=(Monthend(Date)))'}>}empid)

[#4]

Hi AShfaq,

I have tried implementing the formula,you mentioned ,but am getting 0 value.

Please find the attached QVW

[its_anandrjs]

Hi,

Try this expressions

=Count({<Date={'$(=(Monthstart(Max(Date))))'}>}empid)

=Count({<Date={'$(=(Monthend(Max(Date))))'}>}empid)

And what is your expected output in this two

Regards

Anand

[#6]

Hi Anand,

Both are not working,If any one of the expressions work,Its fine.

[#7]

Priyana,

I would do this in your script, create new field(s) to evaluate if the date is a month start or month end. Then just use that flag within your set analysis

EG

Load

empid

,Date

,If(Date=MonthStart(Date),1) As MonthStartFlag

,If(Date=MonthEnd(Date),1) As MonthEndFlag

from xxxxx

Then just make your expression

Count({<MonthStartFlag={1}>} empid)

Count({<MonthEndFlag={1}>} empid)

something like that

Hope that helps

Joe

[jonathandienst]

Hi

I suspect that the problem is you are trying to use it in a chart or table. The set expression is evaluated once for the chart (outside of the dimensions) and not row by row. If you need to do this row by row, you can use count(if()) or set a month end flag during the load.

count(if()):

count(if(Date = MonthEnd(Date), empid))

flag:

LOAD ...

     Date,

     If(Date = MonthEnd(Date), 1, 0) As IsMonthEnd,

     ...

count({<MonthEnd = {1}, empid)

(I include a month end flag in my common master calendar which is in a qvd shared by many models)

HTH

Jonathan

[its_anandrjs]

Hi Priyanka,

Try this way then by adding the fields MonthStart date and MonthEnd Date

LOAD Date,

      MonthStart(Date) as MonStartDate,

      MonthEnd(Date) as MonEndDate,

      empid,

      Type

FROM

mockup.xlsx

(ooxml, embedded labels, table is Sheet1);

And then in the front end write expressions in any text object or the straight table

For MonthStart

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

For MonthEnd

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

Output you get

Regards

Anand

[Anonymous]

Hi,

Please find the attachment...

this attachment will give u the solution .........

Thanks

Venkata Sreekanth