topic How to use Monthend() in QlikView

How to use Monthend()

Anonymous — Mon, 01 Oct 2012 12:02:31 GMT

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()

swuehl — Mon, 01 Oct 2012 12:07:15 GMT

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()

Miguel_Angel_Baeyens — Mon, 01 Oct 2012 12:07:18 GMT

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()

Anonymous — Mon, 01 Oct 2012 12:11:52 GMT

Cool, it works

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

Re: How to use Monthend()

Miguel_Angel_Baeyens — Mon, 01 Oct 2012 12:18:21 GMT

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()

Anonymous — Mon, 01 Oct 2012 12:19:11 GMT

thanks again

Re: How to use Monthend()

Anonymous — Mon, 01 Oct 2012 16:52:13 GMT

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()

Miguel_Angel_Baeyens — Mon, 01 Oct 2012 16:54:31 GMT

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()

Anonymous — Tue, 02 Oct 2012 07:48:33 GMT

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()

Miguel_Angel_Baeyens — Tue, 02 Oct 2012 08:15:33 GMT

Nassim,

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

Miguel

Re: How to use Monthend()

Anonymous — Tue, 02 Oct 2012 10:02:42 GMT

I found my problem
I used "nr_pmc_b" alias QV, and a "nr_pmc" for OLE DB, OLE DB turns out that the fields in capital letters, so I had to use NR_PMC.

simple

thank you.