8 Replies Latest reply: Oct 26, 2017 11:42 AM by Bob Caplan

# Set Analysis for Different Time Frames/Intervals

Hello Everyone,

I am basically trying to get totals for a particular time period. What makes it a little more complex, is that the field (Date) referenced for the time frame is different for each segment. Here is a data sample:

Segment              Date               Total          15 Days      30 Days     90 Days

8000                    1/17/17           8000             1500          2000          4000

8001                    3/18/17          11000            2000          4000          6000

8002                    6/30/17           1500             300             400            500

I am using RangeSum(Above(Count({<event_type={*}>} event_type),0,RowNo(TOTAL))) for the cumulative total. Can I use something like:

RangeSum(Above(Count({<event_type={*},Date={Date+15}>} event_type),0,RowNo(TOTAL)))

for 15 days in, and so on for the other "# Days")? This didn't work of course, so hoping it is just a syntax error, and can be done.

I appreciate any help with this.

Thanks,

Ben

• ###### Re: Set Analysis for Different Time Frames/Intervals

The data sample above is the desired output, by the way.

• ###### Re: Set Analysis for Different Time Frames/Intervals

How does the raw data look like? Would you be able to share a sample?

• ###### Re: Set Analysis for Different Time Frames/Intervals

We use a logger that resembles this (with many more fields):

Segment               Date          Event_type

8000                     3/10/17             33

8000                    3/11/17               33

I use count({<event_type={33}>} event_type) to get the total per segment. This Date is what the master calendar references. The Date in my original post in actually the published_date, which comes from a different table associated by the Segment. Our segments have different published_date's, and I want to see how they perform in 30, 60, 90 days (or whatever). Is it possible to do this in set analysis similar to this?

count({<event_type={33},Date={Date+15}>} event_type)

My other option is to just create a view in PostGreSQL before it gets here, but I have reserves about doing this in a production environment.

Thanks,

Ben

• ###### Re: Set Analysis for Different Time Frames/Intervals

What do you mean Date + 15? You want to see the count for Date + 15th day?

• ###### Re: Set Analysis for Different Time Frames/Intervals

Yes. I actually going to need 15,30,45,60,90,120,150,and 180 days. But an example of any of those should be enough. They would each be their own measure/field in a Table Visualization. That is my vision at least.

• ###### Re: Set Analysis for Different Time Frames/Intervals

Can you share some raw mocked up data and the output you expect from it to help you with a sample?

• ###### Re: Set Analysis for Different Time Frames/Intervals
 Segment Date Event_Type Published_date 8000 1/1/2017 33 1/1/2017 8000 1/2/2017 33 8000 1/3/2017 33 8000 1/4/2017 33 8000 1/5/2017 33 8000 1/6/2017 33 8000 1/7/2017 33 8000 1/8/2017 33 8000 1/9/2017 33 8000 1/10/2017 33 8000 1/11/2017 33 8000 1/12/2017 33 8000 1/13/2017 33 8000 1/14/2017 33 8000 1/15/2017 33 15 8000 1/16/2017 33 8000 1/17/2017 33 8000 1/18/2017 33 8000 1/19/2017 33 8000 1/20/2017 33 8000 1/21/2017 33 8000 1/22/2017 33 8000 1/23/2017 33 8000 1/24/2017 33 8000 1/25/2017 33 8000 1/26/2017 33 8000 1/27/2017 33 8000 1/28/2017 33 8000 1/29/2017 33 8000 1/30/2017 33 30 8000 1/31/2017 33 8000 2/1/2017 33 8000 2/2/2017 33 8000 2/3/2017 33 8000 2/4/2017 33 8000 2/5/2017 33 8000 2/6/2017 33 8000 2/7/2017 33 8000 2/8/2017 33 8000 2/9/2017 33 8000 2/10/2017 33 8000 2/11/2017 33 8000 2/12/2017 33 8000 2/13/2017 33 8000 2/14/2017 33 45 8000 2/15/2017 33 8000 2/16/2017 33 8000 2/17/2017 33 8000 2/18/2017 33 8000 2/19/2017 33 8000 2/20/2017 33 8000 2/21/2017 33 8000 2/22/2017 33 8000 2/23/2017 33 8000 2/24/2017 33

I basically need a running total to stop at 15,30,45, and so on for each segment. They won't be 1 a day, as shown, but wanted to keep it simple. The numbers I am looking for are off to the right under Published_date (Which isn't in this table at the moment). Shown here, the total is 15 at 15 days, 30 for 30 days in, and so on. Does this help? I don't see an insert attachment icon, so please excuse the enormous comment box.

• ###### Re: Set Analysis for Different Time Frames/Intervals

Maybe

count({<Date={'>=\$(Date-15)'}>} event_type)