Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

10 Replies
swuehl
MVP
MVP

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

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
Not applicable
Author

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
Not applicable
Author

thanks again

Anonymous
Not applicable
Author

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
Not applicable
Author

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