Introduction
This article will demonstrate how to quantify on a date axis the availability \ unavailability of products given only the start and end date of each product's availability (See data format in "ProductData,csv" table below).
Business Case
Viewing how many campaigns were available on a collection of dates, how many machines were running or how many products were available in a collection of dates.
Example
Steps
To Achieve this we will be creating 3 tables in our ElastiCube:
- ProductData: (ProductId, StartDate, EndDate) Source table, containing for each product its ID and availability date
- AllDates: (ProductId, dates) Custom table, holds a Cartesian product of all dates given (StartDate and EndDate values) multiplied be all products
- Availability: (dates, avail, notavail) For each date it holds the number of available products and the number of unavailable products
- Add your data in a similar format of ProductData. Make note of the column names as we will be referring to them in the next steps.
- To create AllDates table either:
- Import a table into the elasticube containing all dates in the rance and rename it to "AllDates" - this is the preffered method
OR
- Add a custom table with the following dynamic script (changing table and column names accordingly) - this is the less-preferred method as it may result in 'holes' in the timeline if some dates values are not imported originally
SELECT o.ProductId, c.dates FROM [productData.csv] o, (SELECT a.StartDate dates FROM [productData.csv] a UNION SELECT b.EndDate FROM [productData.csv] b) c ORDER BY o.ProductId desc
- To create Availability table add a custom table with the following script (changing table and column names accordingly):
SELECT DISTINCT a.dates, res1.avail, res2.notavail FROM [AllDates] a, (SELECT a.dates, count(b.ProductId) avail FROM [AllDates] a, [productData.csv]b WHERE a.ProductId = b.ProductId AND a.dates <= b.EndDate AND a.dates >= b.StartDate GROUP BY a.dates ) res1, (SELECT a.dates, count(b.ProductId) notavail FROM [AllDates] a, [productData.csv]b WHERE a.ProductId = b.ProductId AND (a.dates > b.EndDate or a.dates < b.StartDate) GROUP BY a.dates ) res2 WHERE a.dates = res1.dates AND a.dates = res2.dates
- In Sisense web, add a new Columns Widget and choose in the right pane a "Stacked" display
- Create calculated measure "SUM([Notavail])" and name "Not Available". Similarly create "SUM([avail]) and name "Available". Pick your colors and you are good to go!