topic Re: Date Comparison in QlikView

Date Comparison

Mon, 14 Jul 2014 06:18:02 GMT

HI all,

I have a date field in database (Rec_date)

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?

tresB — Mon, 14 Jul 2014 06:21:17 GMT

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 — Mon, 14 Jul 2014 06:39:25 GMT

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 — Mon, 14 Jul 2014 06:40:04 GMT

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