cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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

Community_Admin_0-1634474578778.png

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 
Community_Admin_1-1634474578895.png
  • 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 range and rename it to "AllDates" - this is the preferred 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!
    Community_Admin_2-1634474578955.png

 

Version history
Last update:
‎10-17-2021 05:43 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request