List Of Date Format Conversions
Question A date stored in a source system is not a datetime that Sisense recognizes. Converting it to a Sisense datetime allows powerful front end capabilities (such as drill down, quick functions and more). Answer Julian AddDays(CreateDate(1970,1,1), ((([Julian]- 210866803200000000) / 1000000)/86400)) Julian (Oracle specific) CreateDate(2000+floor([JulianOracle]/1000)-100,GetMonth(AddDays(CreateDate(2016,1,1),Floor(mod([juliandate],1000)))),1) or AddDays(CreateDate(1900+([JulianOracle]/1000),1,1),ToInt(Right(ToString([JulianOracle]),3))-1) Convert Text mm/dd/yy CreateDate( ToInt('20'+StrParts([date],'/', 3)), ToInt(StrParts([date],'/', 1)), ToInt(StrParts([date],'/', 2)) ) Epoch AddDays(CreateDate(1970,1,1), ([EPOCH DATE]/1000)) Date to UNIX ToBigInt((DayDiff([date_field], CreateDate(1970,1,1)) * 86400))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 ViewGet ElastiCube from Process ID
Question I would like to be able to track memory consumption of my ElastiCubes, but I can't find an easy way to link the process ID of my ElastiCube to the cube name. Answer If you open the Task Manager, go to the Details tab and right-click on one of the columns then click on the Select columns option. Find the Command line option. Still on the Details tab, if you search for ElastiCube.exe processes, you can see the ElastiCube name as one of the process arguments (dbname) You can use the following PowerShell (make sure to run as Admin) script to extract all of these into a CSV file: $process = "elasticube.exe" Get-CimInstance Win32_Process -Filter "name = '$process'" | Select CommandLine | Export-Csv -Path "c:\temp\reports.csv"How To Read Dates Before 1970
Question I have to display a field with dates such as 06/05/1851, 07/07/1891 and things like that. I tried to workaround this by using CreateDate() but the data displayed is 01/01/1970. Do you know how I can display the right date in Sisense? Answer Context: Data is pulled from Dynamics365 Online through a CData Connector into Sisense. Dates are stored as EPOCH Unix dates. Therefore, dates prior 01/01/1970 are read as 0. First Step. Cast the date attribute as VARCHAR when loading the entity. See the example below: Second step. Once in the ElastiCube Manager you must extract year, month and date and convert them into integer numbers. Then, use the function CreateDate() to create the new date. I used David Hogeg's approach to create the date. Nonetheless, the approach was adapted to get year, month, day from different places in the string (Hogeg's assumes a YYYYMMDD format, while VARCHAR format is YYYY-MM-DDT....). See the image below:Rolling Trailing 12 Months
Question I'm looking for how to create a chart that shows how my business is performing for the last 12 months at each month-end for a set period. For example, the period ending March '20 would sum up Apr '19 to Mar '20. April '20 would sum up May '19 to Apr '20, etc. Each period in the graph would be the trailing 12-month data. Answer For this, you can create a line/column/area/bar chart with the months as the x-axis, then for the formula use RPSUM or RPAVG, the formula should look like this: RPSUM([measure],12) 12 indicating the number of periods backNumeric Representation Of Dates And Times
Question I have to do a MAX(<ColumnName><of DateTimeFormat>) in the dashboard. The main thing here is that I have to have the last second entry for that specific field. Answer We suggest you would use a float numeric representation of the date, adding the minutes and seconds AFTER the dot. For instance, the date "4/21/20 12:36:56 AM" would become: 2012042100.3656 (In this case, if you'd use a integer representation, the result number will be bigger than an integer and it won't work.) In order to do it, please use this function in HTML: getyear(DateTime)*1000000+getmonth(DateTime)*10000+getday(DateTime)*100+gethour(DateTime)+ToDouble(GetMinute(DateTime))/100+ToDouble(GetSecond(DateTime))/10000How to Split Row On New Line
Question Currently have a one column custom SQL table that has multiple entries per row, separated by a new line. I would like to be able to split or break apart the multiple values so we are left with one normalized table/column that has all of the unique values. Any ideas? Table looks like... Row, Value 1, ABC GHI 2, DEF 3, GHI JKL 4, MNO 5, PQR STU VWX 6, YZ PQR What I would like to get to is this, in a new table. 1, ABC 2, DEF 3, GHI 4, JKL 5, MNO 6, PQR 7, STU 8, VWX 9, YZ Any ideas on how this could be done? Answer CSV file we used for this example: FieldA ABC\nDEF\nGHI JKL MNO\nPQR STU VWX\nYZ I imported this file into Sisense and created three new custom fields (one for each "level" of the cell): StrParts(FieldA + '\\n','\\n',1) StrParts(FieldA + '\\n','\\n',2) StrParts(FieldA + '\\n','\\n',3) Result: Then in a new Custom SQL Expression table, I ran this query to get the final result: You can make the original table invisible as desired. This is just one option. It's a fairly common scenario - you can look online for other SQL techniques to do it and translate the functions to Sisense's syntax as necessary. One general note: You'll need to escape your newline characters by using "\\n" within our text functions.
