topic Re: Set Analysis for Different Time Frames/Intervals in QlikView

Set Analysis for Different Time Frames/Intervals

blpetosa — Wed, 25 Oct 2017 14:29:48 GMT

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

blpetosa — Thu, 26 Oct 2017 14:53:47 GMT

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

Re: Set Analysis for Different Time Frames/Intervals

sunny_talwar — Thu, 26 Oct 2017 14:55:02 GMT

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

Re: Set Analysis for Different Time Frames/Intervals

blpetosa — Thu, 26 Oct 2017 15:17:26 GMT

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

sunny_talwar — Thu, 26 Oct 2017 15:19:53 GMT

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

Re: Set Analysis for Different Time Frames/Intervals

blpetosa — Thu, 26 Oct 2017 15:25:54 GMT

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

Anonymous — Thu, 26 Oct 2017 15:26:43 GMT

Maybe

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

Re: Set Analysis for Different Time Frames/Intervals

sunny_talwar — Thu, 26 Oct 2017 15:30:42 GMT

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

blpetosa — Thu, 26 Oct 2017 15:42:08 GMT

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.