topic Problem comparing Date to MonthEnd in QlikView

Problem comparing Date to MonthEnd

Tue, 18 May 2010 12:00:00 GMT

Hi Guys,

I need help to solve the problem below.

In a trend chart, I use the expression:

=$(vSales)

Where variable is equal to =Sales

Sales is

Sum(SalesUSD)

This expression is working ok.

Now I need to show only the sales up to last closed month, so I am changing the expression as follows, but it doesn't work. I get "no data".

If('29/04/2010'<= MonthEnd(Today(), -1)) It works.

If([Invoice Date]<= MonthEnd(Today(), -1)) It Doesn't work.

If(Date([Invoice Date])<= MonthEnd(Today(),-1)) It Doesn't work.

If(Date#([Invoice Date])<= MonthEnd(Today(),-1)) It Doesn't work.

When I put the expression:

If([Invoice Date]<= MonthEnd(Today(), -1))

In a table, I get the right answer…

What's wrong?

Thanks in advance,

Aldo.

Problem comparing Date to MonthEnd

Tue, 18 May 2010 12:18:18 GMT

Hi Aldo

can you try

If(date([Invoice Date],'DD/MM/YYYY')<= MonthEnd(Today(), -1))

Looks like your [Invoice Date] has another formatting?

Juerg

Problem comparing Date to MonthEnd

Tue, 18 May 2010 12:29:51 GMT

Hi there!

I tried

=If(Date([Invoice Date],'DD/MM/YYYY')<= Date(MonthEnd(Today(),-1),'DD/MM/YYYY'), $(vSales))

but is not working...

Problem comparing Date to MonthEnd

Tue, 18 May 2010 14:04:52 GMT

Hi,

I solved it using Set Analysis:

=Sum({$<[Invoice Date]={'<=$(vLastMonthEnd)'}>}$(vSalesBase))/1000

where the variable vLastMonthEnd stores the last date of last month.

Thanks,

Aldo.

Problem comparing Date to MonthEnd

Tue, 18 May 2010 15:06:27 GMT

Hi Aldo,

Calculations with date demand data formating. Try this:

Inv:
LOAD * INLINE [
Inv_Date
18/05/2010
];

TestDate:
LOAD
If(date(Inv_Date,'YYYYMMDD')<= date(MonthEnd(Today()), 'YYYYMMDD') - 1, 'OK') AS Test,
date(date(MonthEnd(Today()), 'YYYYMMDD') - 1, 'DD/MM/YYYY') as LastDate
RESIDENT Inv;

Problem comparing Date to MonthEnd

Wed, 19 May 2010 07:34:59 GMT

Hi Rubens,

I tried using Date(x, format) and even Date#(Date(x, format))...

It only works with Set Analysis.

Thanks,

Aldo.

Problem comparing Date to MonthEnd

nathanfurby — Wed, 30 Mar 2011 14:25:46 GMT

I believe I was experiencing the same issue but resolved it using the DayEnd() function.

This works because the MonthEnd() function assigns a time stamp of 23:59:59 so it seemed all other dates did not match. My If statement:

If(IslandDate=MonthEnd(IslandDate),IslandDate) - Does NOT work

If(DayEnd(IslandDate)=MonthEnd(IslandDate),IslandDate) - Does work

Thought I'd post for future reference.

Problem comparing Date to MonthEnd

Miguel_Angel_Baeyens — Wed, 30 Mar 2011 14:42:48 GMT

Hello all,

Unlike MonthStart, MonthEnd returns a decimal value, a date timestamp corresponding to the last second of the last minute of the last hour of the date, so for today, it will return an underlying numeric value of 40633,9999. Similar happens to DayStart() and DayEnd(). That's why the above works. Well, both actually return a decimal, although in the start funcions is 0.

So Date('31/03/2011') is not equal to MonthEnd('01/03/2011'), because of this. One work around is to get the dates as numbers and use the Floor() function

Floor(MonthEnd('01/03/2011')) = Date('31/03/2011')

Hope that makes sense.

Problem comparing Date to MonthEnd

nathanfurby — Thu, 07 Apr 2011 10:05:20 GMT

thanks Miguel - useful info.