topic Re: Sum values between two dates in QlikView

Sum values between two dates

Sat, 26 Oct 2013 10:17:50 GMT

Hi All

I want to show sum the values falling between two dates in a textbox . Below are the steps which i followed so far

Created a variable in settings -> variable overview named them as venddate and vstartdate
Added the below expression in text box but its not working

=Sum({$<closeDate = {">=$(venddate) <=$(vEndDate) "}>} sales)

Can anyone help me on to correct the syntax

Many thanks

Ashish

Re: Sum values between two dates

swuehl — Sat, 26 Oct 2013 10:30:30 GMT

How do your closeDate field values look like? What is the format of your field?

And how do your variables look like when dollar sign expanded (you can check this e.g. by using text boxes)?

Re: Sum values between two dates

Sat, 26 Oct 2013 10:38:03 GMT

below is the screen shot of variables

and if i use them directly in text box like below

=$(vstartdate)

=$(venddate)

it shows the correct date

Re: Sum values between two dates

Sat, 26 Oct 2013 10:38:59 GMT

Try this

=Sum({$<closeDate = {">=$(venddate) , <=$(vEndDate) "}>} sales)

Re: Sum values between two dates

Sat, 26 Oct 2013 10:40:06 GMT

or this

=Sum({$<closeDate = {">=$(venddate)"}, closeDate={"<=$(vEndDate) "}>} sales)

Re: Sum values between two dates

swuehl — Sat, 26 Oct 2013 10:44:44 GMT

Your QV functions need to be interpreted before passing to the search string, try

=Sum({$<closeDate = {">=$(=vstartdate)<=$(=venddate) "}>} sales)

Re: Sum values between two dates

Sat, 26 Oct 2013 10:45:50 GMT

i tried it but showing me zero in return

=Sum({$<closeDate = {">=$(vstartdate) , <=$(vEndDate) "}>} sales)

Re: Sum values between two dates

Sat, 26 Oct 2013 10:47:06 GMT

its returning 0 as output

Re: Sum values between two dates

Sat, 26 Oct 2013 10:49:15 GMT

What is the date format in closeDate Field? Does the date format in closeDate field matches the Date format of your variables?

Re: Sum values between two dates

swuehl — Sat, 26 Oct 2013 10:52:41 GMT

Ok,

try using the orginal version:

Your QV functions need to be interpreted before passing to the search string, try

=Sum({$<closeDate = {">=$(vstartdate)<=$(venddate) "}>} sales)

But define your variables with a preceding equal sign:

=today()

=makedate(2012,1,1)

Re: Sum values between two dates

Sat, 26 Oct 2013 10:53:50 GMT

its mm/dd/yyyy

in varibes

i m using today() and makedate function to create date

Re: Sum values between two dates

Sat, 26 Oct 2013 10:58:10 GMT

use today() function in a text box and verify that the date format in closeDate field matches the date format of Today(). Secondly also make sure that the date format in vStartDate is mm/dd/yyyy

Re: Sum values between two dates

Sat, 26 Oct 2013 11:01:14 GMT

its showing correct 10/26/2013

Re: Sum values between two dates

swuehl — Sat, 26 Oct 2013 12:41:49 GMT

Have you tried what I suggested above, evaluating the QV functions in the variable definition?

Re: Sum values between two dates

Sat, 26 Oct 2013 16:21:00 GMT

I tried almost all the options but its still showing zero but if i pass dates like below

=(sum( {$<closedate = {">=1/1/2012 <=6/1/2012"} >} Sales))

it works fine

Re: Re: Sum values between two dates

tresB — Sat, 26 Oct 2013 16:33:31 GMT

Could you share you sample app? See, Preparing examples for Upload - Reduction and Data Scrambling

Re: Sum values between two dates

andrewpettit — Sat, 26 Oct 2013 21:36:48 GMT

I believe the issue is the dollar-sign expansion is returning the date string but you are trying to perform a numeric range search. I have noticed before that it seems sometimes QlikView evaluates text search you might be able to make on list boxes and searches you can specify in Set Analysis differently.

Try the following:

=Sum({$<closeDate = {">=$(#venddate) <=$(#vEndDate) "}>} sales)

The dollar-sign pound should force the dollar-sign expansion to return the numeric value of the dual valued date which has both a string value and number value.

Re: Sum values between two dates

preminqlik — Sun, 27 Oct 2013 03:54:41 GMT

hey ashish try this then,

sum(if(closedate>=vStartDate and closedate<=vEndDate,Sales))

i think u'l take calendar object fo both startdate and end date, in both objects, assign these following values to its range

min= min({1}Date(CloseDate))

max=date(reloadtime())

hope this helps you

Re: Sum values between two dates

Mon, 16 Feb 2015 07:10:20 GMT

try this

for a given day to any number of day. here i have taken n=14 day

Sum

({$<Date_id = {">=$(=(Date_id))<=$(=(date(num(Date_id)+14)))"}>} [field])

Re: Sum values between two dates

Fri, 04 Sep 2015 11:05:24 GMT

Hi all,

I am getting the same, value as 0.

My expression is

Sum({$<Date = {">=$(#vStartDate1) <=$(#vEndDate1) "}>} OnboardCount).
Can anyone tell what the problem is?

Thanks in advance

Vidhya