topic Use of Functions in fields within Set Analysis Help needed in QlikView

Use of Functions in fields within Set Analysis Help needed

Mon, 07 Feb 2011 22:27:39 GMT

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

Use of Functions in fields within Set Analysis Help needed

johnw — Tue, 08 Feb 2011 02:35:02 GMT

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.

Use of Functions in fields within Set Analysis Help needed

Tue, 08 Feb 2011 16:15:26 GMT

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

Use of Functions in fields within Set Analysis Help needed

johnw — Tue, 08 Feb 2011 20:40:45 GMT

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.