Date Comparison

[#1]

HI all,

I have a date field in database (Rec_date)

Head	Rec_date	amount
Bank ABC	30-Jun-2014	1500
Bank ABC	31-Jan-2014	2500
Bank ABC	31-Aug-2014	1200
Bank ABC	30-Jun-2014	7000

I have list box which allow to select Head and a calender in Sheet through which user inputs the date in a variable name to_date

I want to show a sum of amount in text box of only amount of date that user select i am using the following expression

suppose user select 30-Jun-2014 from calender then value of to_date become the same

Now

=SUM({$<Rec_date={>=$(to_date)"}>}amount) which shows data of date >= (Greater then equal) to to_date

this is  working fine shows sum(1200+7000+1500)=9700

=SUM({$<Rec_date={<=$(to_date)"}>}amount) which shows data of date <= (Less then equal) to to_date

this is also working fine shows sum(2500+1500+7000)=11000

but problem arrive when I want to use the equal to condition

=SUM({$<Rec_date={=$(to_date)"}>}amount) which should shows data of date = (Equal to) to to_date but it not show any record

I am wondering why? result should be 7000+1500=8500

can any body help?

[tresesco]

If you want sum for the date selected, without set it should work:

=SUM(amount)

Update: If you want that using set, may be your date format causing the issue. See attached qvw where I used :

=Sum({<Rec_date={'=$(=Date(to_date))'}>}amount)   to give it proper format and date#() in the script.

[Anonymous]

Hi,

Check if the Rec_date is in correct format. Use below script to convert it into date format.

Date(Floor(Rec_date)) as Rec_date

[alexandros17]

Try this:

SUM({$<Rec_date={$(to_date)}>}amount)

if it doesn't work try this other:

SUM({$<Rec_date={$(=Today())}>}amount)

if neither this works then verify that dates have the same format.

Let me know