Count with Date Range

[#1]

Hi, 

I am trying to write some set analysis and am getting stuck on what I thought would be an easy problem. I have a static list of dates that I am loading inline that I want to use as the x axis on a graph. The data has a date field in it for "Last Detected". I would like Qlik to count the number of records that were within the last 30 days of each of my static dates.

This basically gives me a rolling count of the number of records that were last detected in any given time time period.

Thanks in advance for your help.

Sean

[sunny_talwar]

Would you be able to share a sample of what you are trying with the output you are hoping for?

[#3]

Sunny,

The end product would be a bar graph. On the bottom of the bar graph would be the static list of dates that I am loading inline. So think of the dates 11/15/15, 1/2/16, and 2/6/16 loaded inline and each one represents a single bar.

The main data set has an additional date field in it indicating when the event last occurred. The concept is for the bars to represent the count of events that occurred in the 30 days prior to each of the dates loaded inline (so anything with a date of 1/7/16 to 2/6/16 would be in the 2/6/2016 bar). I'm not really sure how better to represent the idea as I cannot get Qlik to show that logic

[#4]

I've generated a dataset that is representative of the problem and uploaded in a qvw. I do not want these bars to all be the total data.

[sunny_talwar]

Not sure, but are you looking for something like this?

Expression: Sum(Aggr(If([Reporting Date] + 30 >= DataDate, 1, 0), [Reporting Date], DataDate))

[#6]

Sunny, thanks something like that is what i'm looking for but that chart returns values of 20 for each date. The values for 12/15 should be 0 in this case and count for 2/2/2016 should have been 3998 (that data got truncated which makes it a bit odd of an example)

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(If(DataDate >= [Reporting Date] - 30 and DataDate <= [Reporting Date], Count(DataDate)), [Reporting Date], DataDate))

Another way to do this is to use a intervalmatch function in the script.

[#8]

Thats exactly it. Thanks a lot for your help.

[sunny_talwar]

That's good, but keep in mind this will be too resource intensive because of a Cartesian product formed on the front end of the application using Aggr() function. I would suggest taking it to the back end if you have a huge database.