topic If and with date in QlikView

If and with date

Thu, 02 Feb 2012 17:52:07 GMT

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.

If and with date

Thu, 02 Feb 2012 17:53:38 GMT

Sorry expression is:

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

If and with date

Thu, 02 Feb 2012 18:13:16 GMT

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

If and with date

Thu, 02 Feb 2012 18:22:40 GMT

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...

If and with date

Thu, 02 Feb 2012 18:23:47 GMT

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

If and with date

Thu, 02 Feb 2012 18:44:42 GMT

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

Re: If and with date

Thu, 02 Feb 2012 20:40:57 GMT

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