Use of Functions in fields within Set Analysis Help needed

[#1]

Dear all,

I have the following code used in an expression (which is working and syntactically correct):

=sum({1 <res_date = {">=01/01/2009 00:00:00 <=08/02/2010 00:00:00"},
 check_date = {">=01/05/2010 00:00:00 <=30/10/2010 00:00:00"}

 >}
 Price)

What I would like instead, is to use the date and Toady function within the following, which is logically correct but has incorrect syntax off cource

=sum({1 <date(res_date) = {">=01/01/2009 <=Today()"},
 date(check_date) = {">=01/05/2010 <=30/10/2010"}

 >}
 Price)

Could anyone shed some light into using the above functions within the segment, or possible point to another relevant thread.

Thanks in advance

Tam

[johnw · Accepted solution]

So res_date and check_date are actually timestamps? I'd personally call them res_timestamp and check_timestamp then, and create actual date fields for res_date and check_date. Then I'd do this:

sum({1< res_date={">=01/01/2009 <=$(=today())"}
,check_date={">=01/05/2010 <=30/10/2010"}
>} Price)

If you really don't want to make date fields, you can't quite do what you want. You can't use a function on the field. You can ONLY look for values for that field. So if it's a timestamp field, you have to match it up to timestamps.

sum({1< res_date={">=01/01/2009 00:00:00 <=$(=timestamp(today()))"}
,check_date={">=01/05/2010 00:00:00 <=30/10/2010 00:00:00"}
>} Price)

That's also assuming your default format for timestamps is 'DD/MM/YYYY hh:mm:ss'. If not, you'd need to specify that format in the timestamp() function above. Same issue for today() if 'DD/MM/YYYY' is not your default date format.

[#3]

Thank you

My case is partially solved.

I restate the following: no function calls allowed where a field should be. Correct? Is this solvable another way?

Thanks

[johnw]

Right, no function calls where the field should be. So while there are probably a lot of possible solutions, I would expect any set analysis solution to take one of two basic forms:

1) Applying the function during the load to create a new field to match the data in the set.
2) Applying the "opposite" function to the data in the set to match the existing field.

I gave examples of each. Not sure what you're asking for if not that.