Problem comparing Date to MonthEnd

[#1]

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.

[#2]

Hi Aldo

can you try

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

Looks like your [Invoice Date] has another formatting?

Juerg

[#3]

Hi there!

I tried

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

but is not working...

[#4 · Accepted solution]

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.

[#5]

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;

[#6]

Hi Rubens,

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

It only works with Set Analysis.

Thanks,

Aldo.

[nathanfurby]

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.

[Miguel_Angel_Baeyens]

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.

[nathanfurby]

thanks Miguel - useful info.