Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
blpetosa
Contributor III
Contributor III

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

8 Replies
blpetosa
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Not applicable

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
Contributor III
Contributor III
Author

SegmentDateEvent_TypePublished_date
80001/1/2017331/1/2017
80001/2/201733
80001/3/201733
80001/4/201733
80001/5/201733
80001/6/201733
80001/7/201733
80001/8/201733
80001/9/201733
80001/10/201733
80001/11/201733
80001/12/201733
80001/13/201733
80001/14/201733
80001/15/20173315
80001/16/201733
80001/17/201733
80001/18/201733
80001/19/201733
80001/20/201733
80001/21/201733
80001/22/201733
80001/23/201733
80001/24/201733
80001/25/201733
80001/26/201733
80001/27/201733
80001/28/201733
80001/29/201733
80001/30/20173330
80001/31/201733
80002/1/201733
80002/2/201733
80002/3/201733
80002/4/201733
80002/5/201733
80002/6/201733
80002/7/201733
80002/8/201733
80002/9/201733
80002/10/201733
80002/11/201733
80002/12/201733
80002/13/201733
80002/14/20173345
80002/15/201733
80002/16/201733
80002/17/201733
80002/18/201733
80002/19/201733
80002/20/201733
80002/21/201733
80002/22/201733
80002/23/201733
80002/24/201733

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.