If and with date

[#1]

I am trying to sum a value between a certain date range for time-series data, grouped by Object (if it falls out of the range I would like the result to be a 0):

=aggr(IF([Date]<MAX([Date]) and [Date] > MAX([Date])-365, sum([Value]),0),[Object ID])

The data consists of Object ID, Date and Value.

The above expression returns 0 when I know for sure the sum should equal a positive integer (e.g. 20).

Please help.

Many thanks.

[#2]

Sorry expression is:

=aggr(IF([Date]<MAX([Date]) and [Date] > MAX([Date])-365, sum([Value]),0),[Object ID])

[#3]

Hi,

I believe for that you can use inmonths() function. This can be used to test if two dates fall between a range say 1 month,2 month, 6 month and at max 12 months etc.

So, you will be able to calculate if they fall in range.

There are some other functions also, to see more about these functions go to help in Qlikview developer and search date and time functions or you can also search inmonths.

Qlikview reference manual will give you more detailed answer.

if you face any issue then just let us know and provide a little more detail.

Thanks

Ashutosh

[#4]

I tried converting with date function just in case. It still won't calculate. I can't use in month because I need to specify the number of days, ie if the date is greater than the date - x days then sum the value in that range...

[#5]

That should've read If the date is greater than the max of the date - x days...

[#6]

ok, if you can post a sample application then I will be in a better position to help because it will help to understand that what will be your scenario and where you want to show it.

..

Ashutosh

[#7]

Please find attached what I'm specifically looking for. Note that the -6 in the expression will be changed...