sum issue with following formula in expression

[#1]

Hi,

I recently created a straight table against a database filtering on date parameters v_StartDate and v_EndDate

When I select v_StartDate as 11/28/2010 and I select v_EndDate as 01/01/2011, I only obtain data from 11/28/2010 to 12/31/2010.

My formula in the expression is as follows:

Sum({< DtTransDate = {'>=$(=Date(v_StartDate))<=$(=Date(v_EndDate))'} >}Retail)

I would assume based on the formula above, that I will obtain all data from 11/28/2010 to 1/1/2011. Yet, this formula filters out 1/1/2011.

Only when I change it to

Sum({< DtTransDate = {'>=$(=Date(v_StartDate))<=$(=Date(v_EndDate+1))'} >}Retail) does this data include 1/1/2011.

I've tested the Value in v_EndDate and it is 40544(which is 01/01/2011).

Why would this happen? Is my formula incorrect?

Any help would be appreciated

Richard

[Oleg_Troyansky]

Just as a blind guess - check if your field DtTransDate is not represented as a timestamp (the easiest way is to Preview your data in the Table Viewer). If it's a time stamp with any decimal part other than zero, your comparison to the result of a Date() function will not include the last date.

Also, it's quite strange that your Set Analsys search condition works fine with single quotes - I though we need double quotes for search.

Another idea is to create a list box for DtTransDate and to try to enter the search criteria manually, and see what happens.

[#3]

thank you for your response. I previewed the data and DtTransDate shows as YYYY-MM-DD with no time stamp.

I also found that If I modify the formula to be

Sum

({< DtTransDate = {'>=$(=Date(vStartDate))<=(=Date(vEndDate))'} >}TicketTotal_c)

then it works and includes 1/1/2011. The original had two dollar signs :

Sum({< DtTransDate = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'} >}TicketTotal_c)

Regards

Richard

Would you know why this change makes a difference?

Would you know why this change makes a difference?

Regards

Richard

[Oleg_Troyansky]

it doesn't make any sense to me... might be just working "by accident" - I advise to test it very well, with different date combinations, to exclude an accidental result...