Set Analysis for Different Time Frames/Intervals

[blpetosa]

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

[blpetosa]

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

[sunny_talwar]

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

[blpetosa]

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

[sunny_talwar]

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

[blpetosa]

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.

[Anonymous]

Maybe

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

[sunny_talwar]

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

[blpetosa]

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.