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_date | creation_Date | revenue | order_number | MonthYear | Account_name |
|---|
| 01/01/2018 | 01/01/2018 | 100 | 12345 | 2018/01 | builders |
| 02/01/2018 | 01/01/2018 | 50 | 12345 | 2018/01 | builders |
| 09/01/2018 | 01/01/2018 | 5 | 12345 | 2018/01 | builders |
| 11/01/2018 | 10/01/2018 | 30 | 56789 | 2018/01 | plumbers |
| 18/01/2018 | 10/01/2018 | 30 | 56789 | 2018/01 | plumbers |
| 20/01/2018 | 10/01/2018 | 30 | 56789 | 2018/01 | plumbers |
| 30/04/2018 | 10/01/2018 | 30 | 56789 | 2018/04 | plumbers |
| 01/06/2018 | 10/01/2018 | 30 | 56789 | 2018/06 | plumbers |
| 15/07/2018 | 10/01/2018 | 30 | 56789 | 2018/07 | plumbers |
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.
