Creating a Year-Over-Year Chart in Sisense
This article addresses the issue of creating a year-over-year comparing chart in Sisense that shows a Key Performance Indicator (KPI) for the current calendar year against the previous calendar year. The chart should display KPI data for the current year's months that have passed and the full previous year.
LAG / LEAD Functions
Question LAG & LEAD Functions are functions that give access to multiple rows within a table, without the need for a self-join. The LAG function is used to access data from a previous row, while the LEAD function is used to return data from rows further down the result set. A perfect example of these functions in use is with the “Sales Order” scenario. A department store is trying to monitor the purchase trends of its customers; more specifically, per customer, what’s the average time it took to repeat an order? For us to do so, we must take a record in the table (Order Date) and compare it to another record in the same table. What are some ways we can do that? In our example, we will analyze & compare the efficiency between the multiple solutions. We will start by addressing the problem in the most common/traditional of solving a problem like this. Answer Steps: 1. Create A New ElastiCube Add Data > Microsoft SQL Server > Connect To Server Pull in Sales Order Header Table "How are we going to manipulate that table to get it into a format that we would want to work with?" Uncheck fields that you don’t need; looking for high-level/important data BUILD! 2. Let's Create A Custom SQL Expression What we are getting here a single customer’s activity. It also orders the Order Date in ascending order (from first date to last) "For this specific customer, I want to be able to see __ amount of days between each order. How can we compute / compare those dates? In order to do so, we want to take the order table and join it to itself, where the customer is the same and the order date is offset to the previous or future date." So, let’s start by leveraging the rank function to assign an order number to every date… The Rank function is important here because within the Customer ID, we want to rank based off the order date, group it per customer, and order by the order date. This basically creates create a system that makes it so that it is comparing itself to itself minus 1. This is what sets up our ranking per order. In order for us to join the table to itself, let’s create a sub query and inside of it, put the entire Customer’s table: (Type Of Join:) What it’s getting joined on: Result: 3. Now That We Have The Order Date & The Total Due, We Can Start Subtracting Them Off Each Other & Start Doing Differences Now you would want to go back to the top; the first “Select” of the sub query and change the claims to: Preview: Now that we saw that this function is working, we can now delete our customer filter, order by filter. Now we get all the differences across all of the customers. Issue With This Solution The main issue with this solution is that it requires a lot of processing due to the two tables. Although we are really using one table, we still have two and the same data is repeated between them. This extra storage isn’t necessary, creates the long processing, and can also create confusion for the customer. Solution 2: Doing A Lookup Function On Itself (Own Data Set). To improve upon the last solution, is there a way to pull in one table and do the manipulation in Sisense, without creating a second table? Answer: Yes! What we can do is take the custom table and add it as a view to the source; that way we only import one table Steps: 1. Duplicate "SalesOrderHeader 1", Rename "SalesOrderHeader 2" BUILD! For us to do a look up on this table, we need to have need to have one key to join. The key is a combination of the customer ID + ranking, that’s what makes it unique. 2. So First, Create A New Field, Record_Rank (Int), Input: BUILD Add Data > Custom SQL Table 3. Head Back Into SalesOrderHeader 2, And Set Up The Different Fields To Create The Key + New Field > CurrCustomerRankKey (Text) Do Entire Build 4. Let's Create A Test Function + Custom SQL Expression > Test Function 5. Go Back To SalesOrderHeaders 2 Table, Do Lookup In There + New Field > PrevOrderDate (Date-Time) + New Field > PrevTotalDue (Float) + New Field > DaysBetweenOrders(Int) + New Field > DiffOrderAmount Schema Build: Solution 3: LAG & LEAD Functions This solution does not include a self-join Steps: 1. Open Up SQL Server (TC SQL) Go to same database (Adventure Works 2012) > create new query Let’s start off the new query as: Now we want to dynamically compare one against the other. Instead of doing a self-join, it tells itself to look at the look at the record/number that is above or below and just pull that value in. So it’s almost like a lookup , but it doesn’t require a join , it just requires you to tell it how to sort the data and just dynamically go up and go down and pull the field that you want in to the current record. This is where the LAG & LEAD functions step in To go back a record, we call the LAG function. Within the LAG function, we must define the field that we want to pull in (order date) and how many records back do you want to go (1). Next we want to group by (partition by) and Order By a type of variety and then order it either ASC / DES. To make our “look up” a bit more distinct we can Result: If we want to compare up against future dates, we would use: And its result would be: What we can also pull in the Total Due and calculate the previous Total Due: Which would require a sub query: To get of the first null, we would enter: Pull in everything from t2.* and now we can calculate our differences (Date Diff, etc): As for getting this entire query into Sisense, we want to create a ViewDays, Hours, Minutes, and Seconds Drill Down
Introduction In this post we'll learn how to create a drill down hierarchy for Days-->Hours-->Minutes-->Seconds. A general quick solution, in case you need the drill mostly for pivot table visualizations, would be to use the ElastiCube to model additional hour/minute/second columns - Add then create a hierarchy using those columns. Day/hour/minute/second Hierarchy: In the ElastiCube Manager, use the Add Custom Field to the table in the ElastiCube which holds your timestamp. Create each field, make sure to set them to Type: Int, and in the editor - Use the GetHour(Date) / GetMinute(Date) / GetSecond(Date) to create each field accordingly (see example below). Build your cube. Example for syntax to create "Hour" custom column: In the web browser, browse to http://localhost:8081 [or the port where Sisense is installed if other than the default]. Go to Admin --> ElastiCubes Choose your cube and press on the right hand side on "Manage Hierarchies" (Documentation: https://docs.sisense.com/main/SisenseLinux/manage-drill-hiearchy.htm) Click on Add Hierarchy, and begin to specify your hierarchy. For example, in my case I chose : Days in OrderDate --> Hour --> Minute --> Second You can mark the "Always include with field" if you want this drill down to always appear when the Days in OrderDate field is chosen for a widget. Your new hierarchy should now look like this: Now, go back to the dashboard and create a new pivot table widget. Select for the rows - Days in OrderDate and whichever additional dimensions you would like to show. Choose some value measure for the values. Apply when done. Notice that when you right click on your Days in OrderDate field, you are now given the possibility to drill down to hours/minutes/seconds.Represent Time in Widgets
As Sisense currently supports daily data, in the following article we will demonstrate how to include time in Sisense Web widgets that is present in DateTime fields. In order to display time format along with date we would Represent DateTime field as Text In order to filter by time format we would Represent DateTime field as BigInt Represent DateTime As Text Add a custom Text field to your table To ensure that the text field is sorted in order, we will format it as 'yyyy-mm-dd HH:mm:ss'. If your datetime field is in the following format: 'yyyy-mm-dd HH:mm:ss', edit the field and insert the following script replacing "[A]" by your datetime field: SubString(ToString([A]),0,IndexOf(ToString([A]),'.',1)) If your datetime field is in another format, edit the field and insert the following script replacing "[A]" by your datetime field: tostring(getyear([A])) + '-' + (CASE WHEN GETMONTH([A])< 10 THEN '0' + tostring(GETMONTH([A])) ELSE tostring(GETMONTH([A])) END) + '-' + (CASE WHEN GETDAY([A])< 10 THEN '0' + tostring(GETDAY([A])) ELSE tostring(GETDAY([A])) END) + ' ' + (CASE WHEN GETHOUR([A])< 10 THEN '0' + tostring(GETHOUR([A])) ELSE tostring(GETHOUR([A])) END) + ':' + (CASE WHEN GETMINUTE([A])< 10 THEN '0' + tostring(GETMINUTE([A])) ELSE tostring(GETMINUTE([A])) END) + ':' + (CASE WHEN GETSECOND([A])< 10 THEN '0' + tostring(GETSECOND([A])) ELSE tostring(GETSECOND([A])) END) Build your ElastiCube, refresh Sisense Web Use this field to present the full DateTime format in a the x-axis of a line chart, rows of pivot table etc. Represent DateTime As BigInt Add a custom BigInt field to your table Edit the field and insert the following script replacing "[A]" by your datetime field: GETYEAR([A])*10000000000 +GETMONTH([A])*100000000 +GETDAY([A])*1000000 +GETHOUR([A])*10000 +GETMINUTE([A])*100 +GETSECOND([A]) Build your ElastiCube, refresh Sisense Web Use this field to filter the original DateTime field ([A]) in your widget to latest date or otherwise.Calculate Average Time Between Transactions
Introduction Calculating transaction frequency for a given dimension is a common requirement across many verticals. e.g. Average Time Between Purchases by a Customer. Average Time between support tickets etc. While this can be calculated in SQL it can get rather complex. A simpler way to achieve this is via custom columns using the rankasc and lookup functions. Instructions For this example we shall consider sales orders. We wish to find the average time in days between orders placed by a given customer. Create the following custom fields #1 DateRank RankAsc([CUSTOMER_ID],[Order Date]) #2 DateRankPrev [DateRank] -1 #3 CustomerRank - A combination of the dimension PK and the rank tostring([CUSTOMER_ID]) + '-' + tostring([DateRank]) #4 CustomerRankPrev tostring([CUSTOMER_ID]) + '-' + tostring([DateRankPrev]) #5 OrderDayDiff daydiff([Order Date], Lookup([ORDERS1],[Order Date],CustomerRankPrev The OrderDayDiff provides the number of days between the current transaction and the previous for the specific client. Averaging this field in the dashboard widgets to obtain the desired numbers.Calculating Values For The Most Recent Date
The point of this article is to provide a method to a value associated with the most recent data in a date selection. Implementation Step 1: in the Elasticube, create a field transforming the date field into an integer using the formula below. Then rebuild the Elasticube (schema changes). getyear([Date Field])*10000+getmonth([Date Field])*100+getday([Date Field]) Step 2: In the dashboard, create a new pivot table. Set the Dimensions to be the field you want to see the value for the most recent date in and the date field. Set the measures to be the sum of the field you want to see the most recent value of and the max of the date integer. In the example above, the we want to see the number of clicks on the most recent date for each campaign. Step 3: Create a filter on the Max Date Integer to see the the top ranked max date. This will return the values for the most recent date. Step 4: Remove the Max Date Measure, you will be left with a table of the Dimension, the max date, and the value for that day.Calculating Business Days Difference Between Two Dates
Introduction This article demonstrates how to avoid counting weekends and holidays when calculating the difference between two dates. Example - Average Shipping Time in Business Days Steps In order to ‘subtract’ two dates and avoid counting the weekends and holidays , we will \ calculate a “business value” for each date within a certain date range. Step 1 Option 1 - Using Pre-calculated Business Days CSV file The business days in the csv file were calculated based on the Bank Holiday for the US. Please verify the dates before using the file. If your use case requires different business days, please create a CSV file with the relevant holidays and calculations. You can also refer to option 2 (using the ElastiCube Manager). The CSV File holds a table with 3 columns: A continuous date list for the entire date range Business Day flag - 0 for holidays and weekends, 1 for business days Business Value - a running sum of the ‘Business Days’ flag Download the 'US Dates with Business Values.csv' file and import the table to your ElastiCube, build it and skip to "STEP 2". Please note that the provided table includes the Holiday Bank between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020 Option 2 (Advanced) - Calculating Business Days in the ElastiCube Manager The external resources needed are: Relevant “Bank Holiday” which can be easily downloaded online A continuous date list according to the Bank Holiday range, the list can be easily prepared using Excel. In the above example I used the US Bank holidays which can be found at https://gist.github.com/shivaas/4758439 Attached are the US Bank Holiday and Date List CSVs that have been used for the example. Download and Import the relevant CSVs to the ElastiCube and build the cube. Please note that the provided lists and Holiday Bank includes dates between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020. Create the business-day flag For each date within the date list, we will attach ‘1’ for business days and ‘0’ for weekends or holidays. For that, we will use a custom column with the following syntax: CASE WHEN lookup([US Bank Holidays 2012-2020.csv],[Bank Holiday],Date,Date) = 'TRUE' OR dayofweek([Date])>5 THEN 0 ELSE 1 END Please perform a Schema Changes build for applying changes for upcoming steps. Calculate the ‘Business Value’ for each date In the previous step, we added the business day flag to each date. In this step we will create a custom table that will incrementally sum the business day flags for each date resulting in a ‘Business Value’. This value will represent the number of passed business-days relative to the first day in that range. For that we will create a custom SQL table with the following syntax: SELECT a.Date, sum(b.BusinessDay) [Business Value] FROM [Date list 2012-2020.csv] a JOIN [Date list 2012-2020.csv] b ON a.Date >= b.Date GROUP BY a.Date Set the tables we added to “Invisible” and build schema changes again. Step 2 - Lookup the 'Business Value' In the Example above, we calculated the average business days it takes to ship for each country. For that, we need the business days difference between the ‘ShippedDate’ and ‘OrderDate’. We will lookup the ‘Business Value’ of each date from the table we created\imported and the difference will be the ‘business days difference’ between those two dates. For that we will use the following syntax: Lookup([Dates Bsiness Values],[Business Value],ShippedDateFixed,Date) - Lookup([Dates Bsiness Values],[Business Value],OrderDateFixed,Date) Note - If the dates are not stored in the same table, each ‘Business Value’ should be looked up to each table separately. Step 3 - Create the widget For the example above, we will select the ‘Ship Country’ as category and add the custom field we calculated as value in Average mode. Download: Date list 2012-2020.csv AverageShippingBusinessDays.dash Ecube US Dates With Business Values.csv US Bank Holidays 2012-2020.csv
