Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rolling up data with set analysis

I have built an app to analyse ad sales, I have gotten on fine with most of the metrics I need to analyse but I am stuck on a couple of them. I am looking for some advice on how to show the information I need.

My data is provided at ad occurrence level (below). An ad is booked, but it may appear more than one time. In the date below, there is an order number of 12345, a DISTINCT COUNT of order_number gives you the number of orders. Order 12345 has 3 occurrences.

Order length

The first thing I need to do is to count how many days a booking has, this is simple in SQL I would take the MAX(Occurrence_date) for each order and subtract this from the creation_Date. In this case, order 12345 would have a booking length of 8 days. I am not sure how to do this within Qlikview, as the data for each order spans over multiple rows. I want to show this as a summary for the dataset based on any selection the user makes as the total booking length. User selection will be MonthYear, this corresponds to the sales period that the OCCURRENCE appears in, not the creation booking date. If I select 2018/01 as a MonthYear selection, I want to find the length of ads that had a CREATION_DATE in that time period.

Bookings over 13 weeks

The second thing I need to do is to build upon the above, I would like to be able to see booking which are > 13 weeks (91 days) in length. In SQL, I would subtract the MAX(occurence_Date) from the creation date I would like to see this at an aggregate level (21 bookings) for example and then a breakdown of all of the accounts and the booking itself (not the occurrences, but rolled up into the booking) which are >13 weeks in length. User selection will be MonthYear, this corresponds to the sales period that the OCCURRENCE appears in, not the creation booking date. If I select 2018/01 as a MonthYear selection, I want to find the ads that are over 13 weeks in length, that had a CREATION_DATE in that time period.


My data set:

occurence_datecreation_Daterevenueorder_numberMonthYearAccount_name
01/01/201801/01/2018100123452018/01builders
02/01/201801/01/201850123452018/01builders
09/01/201801/01/20185123452018/01builders
11/01/201810/01/201830567892018/01plumbers
18/01/201810/01/201830567892018/01plumbers
20/01/201810/01/201830567892018/01plumbers
30/04/201810/01/201830567892018/04plumbers
01/06/201810/01/201830567892018/06plumbers
15/07/201810/01/201830567892018/07plumbers

My data set analysed:

Account name: builders

Order number: 12345

Order creation date: 01/01/2018

Ad length: 8 days

order value: 155

Account name: plumbers

Order number: 56789

order creation date: 10/01/2018

Ad Length : 187 days

order value: 180

I can accomplish this in SQL if a roll up occurrences into orders, I tried to bolt this onto the app as a separate dataset but I get synthetic keys and the app hangs. Any suggestions would be very welcome, I want to use best practise where I can as my originally approach was very hacky.

0 Replies