10 Replies Latest reply: Oct 2, 2012 6:02 AM by nassim kasdali

# How to use Monthend()

Hi,

i creted in my script two flags,

the first :

```if(CalendarDate=MonthStart(CalendarDate),1,0) as Start_Flag   //in the aim to know if is the first day of the month
```

and a second :

```if(CalendarDate=MonthEnd(CalendarDate),1,0) as end_Flag   //to know if is the last day in the month
```

but the second doesn't work !! it gives me 0 to all fields.

can you help me ?!

• ###### Re: How to use Monthend()

Monthend will return the timestamp  of the last millisecond of the last day in the month.

Use

daystart(monthend(...))

when comparing to a DATE field.

Regards,

Stefan

• ###### Re: How to use Monthend()

Hi,

MonthEnd() returns the latest fraction of the latest second of the latest date, i. e.: 40100,9999, and date values are integers by default, so in short, this is likely to work, as long as your CalendarDate field is recognized as a date field:

```if(CalendarDate=Date(Floor(MonthEnd(CalendarDate))),1,0) as end_Flag
```

Hope that helps.

Miguel

• ###### Re: How to use Monthend()

Cool, it works

and i'm wondering why with MonthStart It Worked ???

• ###### Re: How to use Monthend()

Hi Nassim,

Check these examples:

```Num(MonthEnd('01/10/2012')) = 41213,99999998 // 31/12/2012 23:59:59.9999 (decimal)
Num(MonthStart('01/10/2012')) = 41183,0000000 = 41183 // 01/10/2012 00:00:00.0000 (integer)
```

That's why the *Start() function always return an integer value while the *End functions do not. Even if you use the Date() function, the subjacent numeric value will keep the decimal positions, although it will show as a date "31/10/2012", that's why you need to somewhat get rid of them. The Floor() function keeps the integer part of a decimal number or expression

Hope that makes sense.

Miguel

• ###### Re: How to use Monthend()

thanks again

• ###### Re: How to use Monthend()

I come to you for help.
after having used my flag

• if(CalendarDate=Date(Floor(MonthStart(CalendarDate))),1,0) as Start_Flag,
• if(CalendarDate=Date(Floor(MonthEnd(CalendarDate))),1,0) as End_Flag,

I am trying to display the data according to these flag.

• =sum({\$<Start_Flag={"1"}>} nr_pmc_b)
• =sum({\$<End_Flag={"1"}>} nr_pmc)

on its home market but not the end

how does that happen?

• ###### Re: How to use Monthend()

Hi Nassim,

So the flag field Start_Flag does work but not the End_Flag? Are both date fields the same format?

Miguel

• ###### Re: How to use Monthend()

yes of course ,

as you see, the flags works perfectly :

but as you see, i've got my Beginning PMC " sum(nr_pmc) for the first day of the month ,but not the endong pmc "sum(nr_pmc) for the last day of the month

• ###### Re: How to use Monthend()

Nassim,