SQL code to find the distance between two lat-long points on a globe.Additional information can be found here: Calculating Distance Between Data Centers on a Globe2 * 3960 * asin(sqrt((sin(radians(([latitude_2] - [latitude_1]) / 2))) ^ 2 + cos(ra...
I came across an interesting use case with a customer where they had a name and ID column on a dataset: Select a filter value for name, and not id -> show results for nameSelect a filter value for id, and not name -> show results for idIf values are ...
Occasionally, we would want to have a chart show everything except the values selected in a filter. For this use case, we recommend using the following SQL in your chart query. where case when '[Filter_name|Default]'= 'Default' then 1 = 1...
Filters are a great way to slice and dice your data into a variety of views. There are a couple ways you can set default filter values, on the dashboard level and on the query level. Dashboard Level Default Filters If you want all your users to vi...
Let's say we want a printout of our confidence interval for an entire sample (Note, if you're looking for a visual of a confidence interval over time, check out the post here!) The solution here requires Sisense for Cloud Data Teams' Python/R Integr...
The first thing you'll learn in SQL is the order in which we write various clauses in our queries (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, then LIMIT). However, the order in which the database interprets the query is slightly different. Unde...
We worked with a customer recently who wanted to aggregate their data in 5 minute intervals. We had a clean way to implement this: from_unixtime(300*floor(unixtime/300)) How this works: We divide the unixtime by 300 (number of seconds in 5 min...
I've come across a few use cases where customers would like to apply a SQL window function, but are limited to using a version of MySQL that does not support window functions. Traditionally our recommendations would have been one of the following:Cac...
You have you have an R or Python chart but it renders very small. How can you resize it? The Functions to Resize the Canvas These functions will set the max size that the image is capable of rendering. For R, you can append the following line befo...
Sisense has a lot of cool functionality around converting links to a simpler format using Markdown language. However, doing things that require more advanced functionality such as bringing up an email takes a little more maneuvering. Luckily, the int...
Ever seen the following error when materializing a view?SELECT DISTINCT ON is not supportedThe DISTINCT ON clause is a Postgres function which selects the first value for a group given the order by clause. With no order by clause, it gives a random v...
CTEs (common table expressions - also commonly referred to as "with clauses") are an extremely powerful tool in SQL! Similar to subqueries, they can pull "a query in a query," but can be called multiple times within the same query especially easily! ...
Once you have optimized your hyperparameters, it may be nice to create a dataframe that has some of your testing and training data to train your model. Here, we are going to use a KNN model with n_neighbors = 6 and "distance" weights on the iris dat...
Looking to get the average time interval between a start and an end time (ex: Given the start and end times for runners in a race, find the average time it takes for a runner to complete the race)? Find exactly how to write this up in each flavor of ...
There may be several reasons why you want to create a list of dates or timestamps. The primary use case is to left join a dataset that has missing dates onto this full list of dates so you can clearly show where no data is reported.Postgres has a han...
If you have multiple metrics that you want to weigh together, it may make sense to normalize the data so one metric (that is measured on a larger scale), doesn't disproportionately sway the composite score. This SQL snippet (which uses CTEs), is o...
Let's say you have a table that contains a row of expected values and a row of predicted values, and we want to determine the accuracy. What we need to do is find cases where the expected values are equal to the predicted values, count them, and div...
Here are the GeoJSON boundaries for all US counties. Join this data to your datasets to create beautiful GeoJSON Map charts!
Geographic centers of countries in lat-long coordinates. File here.
OverviewCASE WHEN statements provide great flexibility when dealing with buckets of results or when you need to find a way to filter out certain results. You can think of these almost as IF-THEN statements similar to other coding languages. Below you...
Recommended Quick Links
Developers:
Product Feedback Forum:
Need additional support?:
The Legal Stuff
Have a question about the Sisense Community?
Email [email protected]
