How to use Monthend()

[Anonymous]

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 ?!

[swuehl]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[Anonymous]

Cool, it works

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

[Miguel_Angel_Baeyens]

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

[Anonymous]

thanks again

[Anonymous]

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?

[Miguel_Angel_Baeyens]

Hi Nassim,

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

Miguel

[Anonymous]

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

[Miguel_Angel_Baeyens]

Nassim,

I'm unable to see your pictures, can you please attach them instead of posting?

Miguel