Date value in set comparison

[#1]

Hi

Stuck on a set comparison.

I have a calculated previous month end date which is intended to provide a "previous month end" sum

date( monthend( MyDate,-1), 'DD/MM/YYYY') )

This gives me the last second in the month so I added

date( daystart( monthend(MyDate,-1)) ,'DD/MM/YYYY') )

My set expression for previous mont end sum works if I manually set the prev month date

sum ( { 1 < MyDate = {"31/03/2010"} > } MyValue )

Trying to follow the examples in the manual I assumed I would get the correct sum with

sum ( { 1 < MyDate = {$(#=date( daystart( monthend(MyDate,-1)) ,'DD/MM/YYYY') ) ) } > } MyValue )

This however never matches, I assume because I do not get a string value for the date but always magically a date value.

I saw that I could use a variable set to "31/03/2010" but I do not really see a reason why the comparison should not work with the date.

Any hint how I can force to get a string value with the date in it or a better formula for this?

Thanks

Juerg

[#2]

Hi Lorenzo

Kept me nagging, I found a solution which I think works now.

You will need to fill a variable with the previous month end. You have to set in on the selection trigger of giorno and mese. Use Settings, Document Properties, Triggers, Field Event Triggers and the OnSelect action. It needs to set a variable vPrevMonth, it is filled with the trigger action (External, Set Variable), the Value formula is

='"' & left(date(monthend(min([Data riferimento]),-1),'DD/MM/YYYY'),10) & '"'

this will create the date as a string of the form "31/03/2010" or "30/04/2010"

You can check the value in Settings, Variable_Overview.

The expression for the the Prevous column is

sum( {1<[Data riferimento] = {$(#vPrevMonth)}>} [#USIM])

the first "1" will use all data in the tables, the limit is then set by comparing the Data rifirmento with the date of the previous month's end.

Syntax is terrible and requires a lot of trial and error.

Juerg