topic Value amount between dates in QlikView

Value amount between dates

Wed, 10 Jul 2013 19:02:41 GMT

I want to get the sum of all the sales made between two dates.

this is what i got:

=sum({<Date ={">=01/11/2012 <=01/02/2013"}>} Value)

The result is just the value that has this date value: '01/02/2013' if i take out the '=' it returns 0. For what i understood, it isn't looking for the other values, or something like that.

it worked this way:

=sum({<Date ={">=01/11/2012 <=01/02/2013"},month=,year=>} Value)

it gave me the sum, but there are two details here, first that in my proyect i am not using dates like this "01/11/2012" but as a field.A field for the start-date and a field for the end-date. and second that if i use the month=,year= those fields wouldn't work....

I am out of ideas do you have any? thank you.

John Witherspoon

Re: Value amount between dates

Wed, 10 Jul 2013 19:37:20 GMT

I did it, missed the $

=sum({<Date ={">=$(=startdate) <=$(=enddate)"},month=,year=>} Value)

But now have another problem, because that expression is perfect for my selected year and month.

But how could by making that selection substract one month from each startdate and enddate, and of course if it is 01/01/2013 and i substract one month it will be 01/12/2012. in the same expression.

Re: Value amount between dates

CELAMBARASAN — Wed, 10 Jul 2013 23:31:38 GMT

Hi,

Have you tried with AddMonth(date, 1) function

=sum({<Date ={">=$(=AddMonth($(=startdate),-1)) <=$(=AddMonth($(=enddate),-1))"},month=,year=>} Value)

Hope it helps

Celambarasan

Re: Value amount between dates

Thu, 11 Jul 2013 13:02:04 GMT

That was it. Thank you.