Looking for Answers?

Browse our articles to find the answers you need

CDT

A Deeper Dive Into The Redshift Warehouse - Backend Mechanics, Table Size/Sortkey/Distkey Info, And More!

Databases within DatabasesThe Warehouse is a managed Redshift solution which has the ability to store data from multiple origin data sources. Each Redshift cluster can contain multiple databases, and the Wareho

Dynamic Line Or Scatter Chart Based On A Date Range Filter

Single Scatter Point ChartLine Chart for Multiple Data PointsBackgroundLet’s say that we would like a dynamic chart where we can visualize the number of users who started a game session over the course of a mon

Adding A Total Row Or Column To Your Cohort

In many cases, a cohort grid can benefit from showing the rolled-up row or column totals. This can be accomplished within the query by UNION'ing the appropriately grouped columns.Let's start by taking a look at

Weighted Vs Unweighted Averages

When summarizing statistics across multiple categories, analysts often have to decide between using weighted and unweighted averages.An unweighted average is essentially your familiar method of taking the mean.

Manipulating Dates, Datetimes, Unix Timestamps, And Other Date Formatting Tricks In Redshift

Dates aren't stored in the database in the format you want to display? You can use the Redshift TO_CHAR() function to create a string-formatted extract in order to display dates however you want! Note that in R

Test For Normal Distribution Of Data With Python

One of the first steps in exploratory data analysis is to identify the characteristics of the data, importantly including a test for distribution patterns. In this example, learn how to check if your data is no

Filters In Case When Statements

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 name Select a filter value for id, and

CASE WHEN Statements

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 simi

Get Yesterday's Date

Often times, we want to analyze data with the date from yesterday. How do we auto-populate yesterday's date? There are a few formats that we can use! The syntax below lets us get yesterday's date with timestamp

Confidence Interval Printout - Python

 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 Peris

Funnel Charts In Python

Funnel charts are a great way to represent any drop-offs in sample size throughout a series of steps. Using a little bit of Python handiwork in Sisense for Cloud Data Teams' R/Python integration, we can easily

Calculating Trimmed Means (SQL And Python Variations)

Data can oftentimes have extreme outliers, which can heavily skew certain metrics, such as the mean. One way to get around this is to using a trimmed mean. Using a trimmed mean, users will remove the top and bo

Chart Type - Radial Bar Chart In Matplotlib (Python)

Here's a script that takes a data frame with two values, the current and benchmark, and returns radial bar charts to plot progress toward a goal. You can also choose a color using the color_theme parameter that

Loose String Matching (Levenshtein For Redshift?)

Perhaps a bit tangential to text mining, but has anyone found a way to efficiently do string analysis in Redshift?  Ideally something like levenshtein() or levenshtein_less_distance() in postgres to return stri

Generate Series Of Dates In Snowflake

As Snowflake doesn't have a native generate_series function, here is our solution to generating a table of incrementing dates, starting from the current date, in Snowflake. It is currently set to generate 1095

Text Mining With SQL

One of the recent questions I had to answer focused on analyzing text data. How have you solved this problem in the past? I was not sure how to create an optimized solution for both efficiency and completeness.

Sankey Diagrams With Plot.Ly In Periscope

Sankey diagrams are a very easy-to-digest way to look at the flow of objects between different points. The example we will walk through here is a basic Sankey overview, leveraging data from a fictional gaming c

A Beginner's Guide To Linear Regression Models In Python

Does your team prefer Python over R? Or are you looking to brush up your on your Python skills? We'll walk through a simple example of a linear regression model using the scikit-learn library in the Python/R In

Remove Whitespace Margins From Plotly Charts

Plotly is wonderful, but sometimes you end up with a chart that looks like it's too small, or that it's not taking up all the space. Why does it do that? In this post, you'll learn how to fix that:With Python,

Donut Charts — Plot.Ly

The above example is taken from plot.ly's documentation here. A few updates have been made to the example to make it easier to use within the Sisense for Cloud Data Team's Python/R integration.Pie charts with a

Horizontal Funnel Chart — Plot.Ly

Visually it can be preferable to display a funnel chart horizontally. The following code outlines how to do this.  Note, your SQL output must have 2 columns titled as follows: phases: the names of each stage

Connecting Data Points With Smoothed Lines (Spline Curves) In Periscope With Plot.Ly (Python And R)

Ever looked at a chart like this:and thought to yourself that the data would make much more sense with curved lined connecting the points, rather than straight lines? Well fear not, because plot.ly makes it eas

Waterfall Charts -- Plot.Ly

Waterfall charts are a great way to highlight changes in your data between steps or periods. The following python script easily plugs in with your SQL output, simply paste this into your Python 3.6 code box pro

Country Centroids

Geographic centers of countries in lat-long coordinates. File here. 

Overlapping Area Chart — Plot.Ly

We can create overlapping area charts in Sisense for Cloud Data Teams with the Plot.ly integration! These offer a better way to see the exact value per shaded region than stacked area graphs. If your purpose, h

Progress To Goal (Radial Bar Charts) — Plot.Ly

Looking to create a radial bar chart with plot.ly? Look no further! Radial bar charts are a great way to show progress to a goal. The following python script easily plugs in with your SQL output, simply paste t

Boxplot -- Plot.Ly

Boxplots are a widely recognized chart type and are excellent for quickly and succinctly visualizing distributions. The following Python script, leveraging the plot.ly library with Sisense for Cloud Data Team's

KPI Chart — Plot.Ly

Contextualizing KPIs is a must. For instance, if a company has 500 users, how does that compare to the number of users last week? Using Sisense for Cloud Data Teams' Python Integration, we can create customized

US County GeoJSON

Here are the GeoJSON boundaries for all US counties. Join this data to your datasets to create beautiful GeoJSON Map charts!

Sankey -- Plot.Ly

Sankey diagrams are a very easy-to-digest way to look at the flow of objects between different points. The below script takes a SQL output with these 3 columns: unique_id: the unique identifier for that entit

Gantt Chart -- Plot.Ly

Gantt charts are a great way to show which projects and tasks different teams are working on at a given time. This chart type is heavily used by Project Managers, Task Managers, and anyone else managing multipl

Use A Chart-Level Filter For Your Y Axis - Plot.ly

Want to create a chart-level filter, allowing you to change your Y axis? Say hello to your new favorite library plot.ly! Sisense for Cloud Data Teams' Python and R integration allows you to use Plot.ly (as well

Visualizing Text Data With WordClouds

A popular way of visualizing text data is using WordClouds. Using Python's WordCloud library this can be done with a column of text data and a few lines of code.# SQL output is imported as a pandas dataframe va

Visualizing Forecasting Data (Prophet) In Plot.Ly — Python

Looking to visualize your forecasting data? The code snippet below illustrates how you can get an informative and aesthetically pleasing visual, like the one above! Note this uses the plot.ly library as well as

Determine The Fraction Of Rows That Meet A Condition (Ex: Calculating Accuracy On A Test Dataset In SQL)

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 th

Setting The Marker/Bubble Size In Bubble Charts

Link to raw dataBackgroundOne of the more frequent questions that we get asked on Sisense for Cloud Data Teams is how a user can alter/set the size of the bubbles/markers beyond the default “weight options” as

Normalizing Parameters With SQL

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

Extract A Phone Number From A String In Redshift

Sometimes, phone numbers don't come in a format that easy for extraction. It requires a little bit of magic with Regex expressions in Redshift to get what you're looking forLet's say I have this table, called "

Generate Series Of Timestamps (1 Second Intervals)

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 wher

Dynamic Line Or Scatter Chart Based On A Date Range Filter

Single Scatter Point ChartLine Chart for Multiple Data PointsBackgroundLet’s say that we would like a dynamic chart where we can visualize the number of users who started a game session over the course of a mon

Rolling Averages Using Window Functions

Want to show a rolling average for your data, but not the granular breakdown? We can use the average window function to run this calculation.First, I created a CTE that contains the number of new users per day

3D Scatter Plots In Plot.ly

NOTE: you don't need to be doing Machine Learning to make a cool 3D plot like this! This post takes the output of a machine learning model and shows the results on a 3D plot, but you can plot almost anything wi

Text Boxes With Different Fonts!

Sisense for Cloud Data Teams has a feature called Text on Dashboards that allow users to add text boxes as banners, announcements, and descriptions of charts! However, they currently only support one font. With

Average Time Interval Between A Start And End Time

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

MySQL’s Sec_to_time() Function In Redshift

In MySQL, it’s easy to convert an integer representing a number of seconds into a string representing a human-readable amount of time.This is especially useful when you want to compare two timestamps, for examp

Introduction To Hyperparameter Optimization - Machine Learning

There are lots of knobs (a.k.a hyperparameters) we can turn when coming up with a Machine Learning model. in the script below, we take the well-known iris dataset, and play around with different hyperparameters

Prepping A Dataframe To Hold Your Training And Testing Data - Machine Learning

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

Walkthrough: Unaggregate/Disaggregate Data

Let’s say you have some aggregated data and want to break it out into multiple rows. Maybe the original data source provided the values in this format, or maybe it was produced by another SQL View using Listagg

Conditional Formatting On Entire Rows With Plot.ly

A common request is to apply conditional formatting on entire rows based on a value in a given column. Currently, with Sisense for Cloud Data Teams charts, you can only conditionally format columns. Python char

SQL Alert - Tell Me If A Value Dropped Since Yesterday

SQL Alerts are very handy to keep track of any critical metrics. Oftentimes it makes most sense to do a relative comparison of your data. For instance, "alert me if my metric dropped between yesterday and today

Creating A Table Of Variables In SQL

MySQL aficionados have likely leveraged its ability to declare variables. Unfortunately, this capability isn't supported by all flavors of SQL. For such situations, we can still achieve a similar result by crea

Plotly Choropleth With Slider (Map Charts Over Time)

Plotly is great for its interactivity on visualizations! With choropleth charts we can add a slider to the bottom of the chart to see how data changes over time on a map. The end user can drag the slider across

Everything About CTEs!

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 w

Changing Line Styling -- Plot.ly (Python And R)

Wanted to alter the thickness of the lines in your series, add markers to your lines, and make them dashed or dotted? The plot.ly library support on Sisense for Cloud Data Teams' Python/R integration allows you

Fixing DISTINCT ON Not Supported

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

Hyperlink To Email - Gmail, Yahoo, And Outlook

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 l

Custom Aggregation Filter

Aggregating data by various periods of time is made easy with Sisense for Cloud Data Teams' built-in date aggregation filter. Sometimes, however, you may wish to group your data by different periods of time. In

Every Kind Of JOIN

More often than not, you'll need data from multiple different tables to create the perfect chart! There are many ways to do this but the most common way in SQL would be to use Joins! There are four basic types

Compounding Values With Dynamic Multipliers

Coming from the world of spreadsheets, you may have defined a set of values recursively with ease, referencing the previous value plus another variable to calculate a new value. Something like this:In the above

Plotting A Gradient Line In R

Color is an extremely useful visualization tool. On a line chart, a color gradient can be used to emphasize the highs and lows. Using the Python / R integration in Sisense for Cloud Data Teams, we can definitel

Python Bullet Charts

I've enjoyed creating bullet charts in Sisense for Cloud Data Teams using the technique outlined in this community post here. However, if we seek to further customize the look of a bullet chart, the R/Python in

Radar Charts In Python

Radar charts are an effective way to quickly grasp a profile of an individual item. You may have seen them before on video games or personality tests. Some other great use cases are: Different attributes to m

Communicating Deadlines Through Calendars In Python

Ultimately, the most effective way to show upcoming deadlines is to display a calendar. We can easily build this out using Sisense for Cloud Data Teams' Python integration (Python code below!)The Python script

Resizing And Scaling Charts In R Python

You have you have an R or Python chart but it renders very small. How can you resize it? The Functions to Resize the CanvasThese functions will set the max size that the image is capable of rendering. For R, yo

Gantt Charts Version 2 - Individual Workstreams

While some users like showing Gantt charts that allow overlapping dates (see community post here), many would prefer a visual that clearly shows individual work streams, especially in organizations where member

Contract Adherence KPI Chart

MotivationA frequent business question often posted by Sales teams is "Where are our customers currently in terms of contract adherence?" This helps inform next steps and conversations for an account. With Pyth

Dealing With Missing Values In Python

Python pandas offers a few different options to deal with null values. Based on your dataset, there will likely be a preferred method to account for null values that 1. accurately represents your data and 2. pr

Window Functions In Python

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

Choropleths Made Easy In R

Choropleths are fancy names for maps with heatmaps, probably because no one wants to say heatmap maps and feel redundant. In R there are packages that make Choropleth objects. choroplethr  holds the shape files

Beware Of Many To Many Joins!

Row counts looking off or data not looking quite right after a join? Well, you could possibly be doing a many-to-many join if the columns in your join conditions aren't distinct!Let's take the following example

Part 1: Understanding The NVL/COALESCE Expression

What are Nulls?The null value can be confusing--does it represent a zero, an empty space, a blank placeholder? In SQL, null represents an “unknown” type or the absence of a value, and the use case and queries i

Part 2: Understanding The NVL/COALESCE Expression

Now, on to the second part of our journey with the NVL/Coalesce Expression!Coalesce Expression & Return TypesWhen a Coalesce/NVL expression is applied, all inputs/arguments should be the same datatype, as the C

Additional Date Aggregations: Aggregating By 5 Minute Intervals

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 3

Window Functions!

Window Functions are a great way to analyze your data in a different way! Similar to other calculation statements in SQL, window functions allow you to do a calculation across a set of rows or columns. The diff

Sorting Stacked Proportional Bar Charts By Magnitude

By default in many charting libraries and tools, series within stack bars are arranged in the same order. Looking at the image below, blue is above orange, which is above green, which is above purple.But let's

SQL Order Of Operations

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 interpre

Radial Funnel Chart

Here is an alternate visualization of a funnel chart - instead of using bars, it depicts the different steps as concentric circles.How to use: define an array in the extract() function of the values you'd like

Conditional Formatting For Rows With Similar Results

So you have a table neatly ordered by users, you proudly present it to your boss, waiting for the praise, but you forgot: She’s desperately near-sighted! How can she tell when the user_id changes without having

Visualizing Statistical Significance In Samples Over Time - Python And R

Let's say you're tracking a KPI, and want to see if any changes in your KPI are statistically significant. It would be nice to mark these points on your line graph to call attention to these particular increase

Using Python To Plot Only The Rolling Average Line

Want to show a rolling average to your data? Using the Sisense for Cloud Data Teams Python / R integration, we can accomplish this with a single line of Python code.Here are the first few rows of our SQL output

Radial Bar Chart With Pending Pipeline

Radial bar charts are a great way to visualize progress charts - but sometimes it's helpful to be able to visualize that progress with the additional context of more pipeline as well.Input: you will need to pro

Your Guide To Multivariate Linear Regressions In R

Once you’ve gotten familiar with bivariate linear regressions (one explanatory variable, and one response variable), you may want to delve into seeing how multiple explanatory variables contribute to your respo

Confidence Interval Printout - R

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 Sisens

Showing A Confidence Interval - R And Sisense For Cloud Data Teams Visualization

FOR CLOUD DATA TEAMSConfidence intervals are a favorite among many professionals with a statistics background. We can use the R integration in Sisense for Cloud Data Teams to show the Confidence interval as a s

Text Pivots - Pandas

****The code for this solution is leveraged from this source ***Let's say you want to pivot a table, but you want the actual data in the pivoted cells to be a string.Take this SQL output as an example. Note, if

Transposing Tables In R And Python

Transposing a dataframe in R is as simple as 2 quick lines of code. The first is to actually transpose the table, and the second is to name your column headers.Below is the dataframe we will be transposing. Thi

Your Guide To Default Filters

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 Filt

Prompt Users To Enter A Filter Value

Oftentimes we want our end users to select a filter in order to use a dashboard. One of the ways we can do this is by building it into then chart SQL, as shown below:select case when '[source_test|default

Filter Out Selected Filter Values

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_na

Filters In Case When Statements

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 name Select a filter value for id, and

Distance Between Lat-Long Points

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]

[Customer Contribution] Stars For Ratings

This post is from one of our customers!This post is from one of our customers!Use this case statement in a snippet with a rating parameter to change numerical ratings to stars:case when ROUND([rating]) = 5

[Customer Contribution] Generate True Count From Start/End Date Range

This post is from one of our customers!I work for a company that uses a subscription revenue model, where subscriptions are stored in postgres as start and end timestamps. For our insurance & billing department

[Customer Contribution] NPS

This post is from one of our customers!Some handy snippets for calculating NPS and accuracy. [nps] ((promoters/total ) - (detractors/total))[nps_var] ((1 - [NPS]) ^ 2 * promoters/total + (0 - [NPS]) ^ 2 * neut

Up Arrow Or Down Arrow For KPIs

Use this case statement to add up and down arrows to your metrics, for instance:case when [value_field] < lag([value_field]) over(order by [order_field]) then '▲ ' when [value_field] = lag([value_fie

Correlation Coefficient SQL Snippet

Want to create a convenient Sisense for Cloud Data Teams SQL Snippet which emulates a function and returns the (Pearson) correlation coefficient between two variables?Create a SQL Snippet called 'correlation_ca

[Customer Contribution] Search For User By Radius SQL

This post is from one of our customers!Looking forward to that free shirt! In the mean time, here's a fun sql snippet I wrote to find select users (for something I was working on) by radius of area:SELECT l

Retention Cohort Without An 'All Activities' Table

Our blog has a post about calculating retention, but this assumes I have a table with user activity from every single day per user, which is, more often than not, not the case. So I put together a version that

Clean Text To Alphanumeric Characters Only

SQL Code to clean strings to alphanumeric characters only. Useful for cleaning survey data and data which may contain special/foreign charactersregexp_replace( regexp_replace(lower([text]), '[^a-zA-Z ]', ' ')

Un-Correlating Correlated Subqueries

A correlated subquery is a subquery that references a field from a table that is not included within the subquery. In other words, there are references to the "outer" query in the "inner" query. Correlated subq

Creating A Split Funnel Chart In R

Funnel charts are a common way to visualize conversion for various stages in a business process.Sometimes, it could be interesting to visualize a funnel split by demographic - I recommend just limiting the spli

Using R To Plot Only The Rolling Average Line

Want to show a rolling average to your data? Using the Python / R integration, we can use a few quick lines in R to accomplish this.Here are the first few rows of our SQL output - a list of dates with the numbe

Percentile Curves -- Another Way To Show Distributions

Note: This solution requires Sisense for Cloud Data Teams' Python/R integrationLet's say you have data set that is heavily skewed, like this hypothetical data set here plotting the amount users paid on a fictio

Chart Type -- Number Overlays With Secondary Reference Number

Contextualizing KPIs is a must. For instance, if a company has 500 users, how does that compare to the number of users last week? Using Sisense for Cloud Data Teams' Python Integration, we can create customized

Table Of Contents

We've found here at Sisense that it's often very helpful to have the default dashboard on a site include a table of contents for even quicker navigation to key dashboards, or even a table of hyperlinks to offer

Using Filters To Change The Group By Field

We can leverage filters to even change the field we are grouping by! This technique allows users to effectively see many different analyses, all in one query.Below we have a query looking into the number of use

Sentiment Analysis - Scored Words

List used in blog post on sentiment analysis of Donald Trumps Tweets: https://www.sisense.com/blog/text-analysis-with-trump-tweets/File here.

Simple US Outline GeoJSON

Simplified outline map of the United States in GeoJSON format. Join this data to your datasets to create beautiful GeoJSON Map charts!File Here

US Area Code City And Lat/Long Information

Contains the following:Area Code, City Name, "State Name",US,Lat,LongSourced from https://github.com/ravisorg/Area-Code-Geolocation-Database/File Here.

What Pro Sport Should You Play?

[Written by Kyle Demeule]As a Canadian I was always amazed at some of the salaries baseball or football players we're able to get (Jason Heyward getting 21 million year? He plays outfield right?), especially co

Chart Type - Histogram

Histograms are useful for showing the distribution of a data set while aggregating up to see more general trends. Here is the SQL code (in a handy parameterized snippet!) for binning numeric data and creating h

Chart Type - Funnel Chart

The funnel chart can be created as a variation of the bar chart. Here is an easy SQL Snippet to take your table, the values, and the series of steps. It will calculate the left and right padding for all values

YTD Sum Vs Prior YTD Sum

This parameterized snippet will calculate the sum of a metric for the current YTD vs prior years to current date (day of year).Name Ytd_sum(Table,Date_field,Value_field)select year , [value_field] , ([value_

Chart Type - Waterfall Chart

Waterfall charts are a beautiful way to show a positive and negative stack in series. This sums to a running total over the series of values. It allows a consumer to quickly see the rise and fall over the serie

Chart Type -- Control Chart

Not everyone has to be a six sigma black belt to appreciate and use a well-designed control chart. The top and bottom red lines indicate a standard deviation away, the green line indicates the average, and the

Visual Analysis - Comparing KPI To A Benchmark

What methods do you use to effectively communicate "Here is the KPI versus a Benchmark"? One of my favorite is to use the chart series type to indicate the difference between a realized KPI and the theoretical

Chart Type - Bullet Charts

I love a great benchmark or comparison to goals. One solution that Kyle on my team came up with was a bullet chart. I standardized this with a Parameterized SQL Snippet to do all the transformation heavy liftin

Excluding Data That Isn't Fully Baked

Have a dataset where you want to display everything but the most recent time period? This is handy to do if you want to only disclose KPIs once data has been given the chance to fully mature. (For example, If w

Using The Built-In Date Range Filter To Dynamically Update BigQuery Results

BigQuery, unlike other flavors of SQL, breaks out its tables by day. Each table has a suffix with the date included, but what if you want to allow your end-user to dynamically query only tables from certain dat

Byte Conversion

We created a parameterized snippet to take a raw amount of bytes and convert it to Kilobytes, Megabytes, Gigabytes, and Terabytes accordingly. Snippet Name: Byte_Conversion(byte_field)Snippet Text: case when

Chart Type -- Period Over Period Change

An important analytical technique is to look at the rate of change over time. An easy graph to investigate this is a bar chart with the period over period change in percent or value. In this snippet, you can ca

Chart Type -- Gantt Chart

People love to see their schedules. So a Gantt Chart can be a great addition to a dashboard to offer context. Here is a Parameterized Snippet and set of toggles to get a Gantt Chart going with little effort.Bas

Chart Type - Gauge Chart

Everyone loves to see values relative to benchmarks in a Gauge Chart format. The speedometer gauge is such a common way of people ingesting information that it has become commonplace in the Business Intelligenc

Snippet - Month Versus Month In Prior Year

Here's a Parameterized Snippet to calculate the metrics versus the same month in the prior year. Ideally the metrics have already been calculated in a CTE or View and can easily be plugged into the template.Nam

Controlling Cohort Grid Ordering Via Cross Joins

When determining the ordering of rows and columns in cohort grids, Sisense for Cloud Data Teams examines the order of your underlying data. In most cases, this produces a result in the desired order. Sometimes

Creating A LIKE Or Wildcard Search Filter, And Extending Search Functionality With Regexp_replace()!

Ever needed to perform wildcard filtering on a string column? You can achieve this using our Direct Replacement Filters plus the trick of concatenating a '%' onto the filter in the WHERE clause. Let's say you h

Creating A "Select All" Condition For A Multi-Select Filter!

Often, users will have a large number of filter values selected in a multi-select filter, and rather than having to deselect or 'x' out every filter value, you may want to quickly check the full, unfiltered dat

A More Robust LIKE/SIMILAR TO/Wildcard Filter At The Dashboard Level With A Matching Expression Filter!

You may have seen our earlier post on how to create a LIKE wildcard filter by concatenating the wildcard operator (%) onto a filter value. One of our engineers suggested a better method - using a Matching Expre

Visualizing Densities On A Map

With maps, I like to use color to easily show relative densities and concentration. For this, use a case when statement to split your regions into groups, like so:case when density <= 10 then '1-10'

Passing Through Filter Values With Apostrophes

Let's say you have a list of names that you want to filter by, but one of the names contains an apostrophe. You would rather not remove the apostrophe and have the filter values and results displayed with the i

Adding Pagination To Tables With Filters

Sometimes it's important to be able to access large amounts of data directly in a table. For chunks of data that can't be safely loaded in a browser, one option would be to split the results in multiple charts

Using A Case/When Clause With Default Filter Values

Sometimes when using direct replacement filters, you may want to filter or manipulate your data differently depending on the value or values supplied by the filter. Consider the following example:select * fro

Assigning Colors Across Multiple Charts Using Ggplot2

When creating dashboards, we often would like to ensure that the same colors correspond to the same series across multiple charts. This could be desirable because our series could have associated brand colors,

Using Ggplot2 For Charts With A Similar Look To Periscope Data's Default Charting

If you have a mix of visualizations using Sisense for Cloud Data Teams' default charting options and R/Python charts, ensuring a uniform look gives a cohesive flow to the entire dashboard.By playing around with

Accounting For "No Rows Returned" Via Python

So you've built a beautiful dashboard but your user decides to use a filter combination that returns no rows. Sometimes it's not even your users, it may just be that you have a structure where not all charts sh

Graphing Plot.Hist() As A Chart On Your Dashboard

Plot.Hist( ) is a very useful function for creating histograms in Python. However, this is ultimately a bar chart that can be graphed by Sisense for Cloud Data Teams as well, and would better match other charts

Dynamic Conditional Formatting With Filters And Python

Filters are a useful way to hone in on a specific subset of data, but let's say that you prefer highlighting records that meet the filtering criteria, rather than eliminating others from view entirely. This is

Embed URL Generation Made Easy With Python Script

Creating an embed URL is a multistep process that can easily be botched along the way. Finding where the mistake was made can be tricky, and the generation of multiple embed URLs can be a painful process to rep

Display df.describe() Output In A Dataframe

The .describe() function in Python is really handy for exploratory analysis! Sometimes, you would want to display this as a table on a dashboard, but how? The output is typically printed out as text, not as a d

What Version Of Each Library/Package Is Sisense For Cloud Data Teams Using? [Python And R]

Quick to figure out :) Run the following code within the Python/R editor. (Write Select 1 in the SQL editor to be able to use the Python/R editor)import pkg_resources; installed_packages = [(d.project_name, d.v

Custom Error Messaging In Python For No Rows Returned

Sometimes when using python code, there will be cases when no data is returned. To account for this in python if you can first check if the dataframe has empty rows with.if df.size == 0: (then run all of my a

Stack Rank — Plot.Ly

A stack rank chart shows the relative rankings of different objects over time. These are useful for determining on whether rankings are fluctuating highly or staying fairly consistent.The SQL output must have t

Wildcard Filter For Comma Separated List Of Values

You can easily create a wildcard filter that allows you to filter by a word or series of words on your chart using this post on Wildcard Filters. However, let's say you have a list of comma separated filter val

Troubleshooting Best Practices

As a member of the Solutions team, I rely on several handy troubleshooting tricks to debug numerous queries. I've put a few of my favorites below - feel free to comment below with any additional debugging tools

A Beginner's Guide To Linear Regression Models In R

New to R? Not a problem! You can still deliver valuable insights to your team using a few simple functions. This article walks through how you can use the Python and R integration along with its charting capabi

Regression Options To Analyze Your Data

Oftentimes, we want to explore the relationship and correlation between multiple variables. Below are three different methods you can achieve this in Sisense for Cloud Data Teams, along with important tips to k

RBAC - Example Of Using Access Roles And Department Roles

With the Role Based Access Control, Admins have more control over what their users can do and on which dashboards. With memberships to multiple roles, Admins can specify the dashboards each user can see and wha

User & Group Management API Call Examples In Python

With the release of our User and Group Management APIs, we can now create, read, update, and destroy users and groups programmatically. CRUD! Here's the official documentation.Here are some super simple example

User & Role Management API Call Examples In Python (RBAC)

Similar to the User and Group API, Sites that have RBAC enabled can use the User and Role Management APIs for RBAC allows us to create, read, update, and destroy users and roles programmatically. Here's the off

Pass Filter Values Through Drilldown Mapping

Drilldown DocumentationDirect Replacement Filters DocumentationWhen drilling down from a chart to another dashboard, we have the option to pass any of the column values as a filter value in the destination dash

Creating A Free-Input Email Filter

A dashboard filter that allows users to paste in an email address requires special consideration. In particular, as mentioned on our filter documentation, special characters except hyphens and decimals are stri

Embedded Drilldown Example

The following is a simplified example on how to create an event listener in order to implement a Drilldown in an embedded chart. Refer here for our official documentation on our Embeds Refer to this community p

Render API Examples

The Render API is perfect if you want to download a nicely formatted and dynamic PDF image of an entire dashboard. The Render API is an add-on feature in Sisense for Cloud Data Teams. Here is the official docum

Set Up A Preemptive Auto Archive Alert

With Auto Archive, unused dashboards, views, and uploaded CSVs will automatically be archived after a number of days defined in site settings by an administrator. To make sure nothing gets accidentally archived

Alerts In Slack

Sometimes users prefer to receive alerts via Slack channel as opposed to email.  While alerts are only built for email, we can receive alerts in Slack using Slack's 'Email' app. 1. Click 'Add Configuration' 2.

Top 4 Reasons Your View Isn't Materializing

Ever have a SQL view that won't seem to materialize? Getting that dreaded red X even when results are showing in the View editor? Well follow the below instructions to turn those red X's into green checks!1. YO

Notes On Data Latency For Embeds

When opening an embedded dashboard, we check the most recent refresh timestamp for the given filter set, and if this is less than the specified “data_ts” parameter, then we will refresh the embedded dashboard a

Have You Seen Dependency Mapping Yet?

If you’re anything like us—and if you’re reading this, you probably are—you’ve found yourself in a position where you’ve created 60+ interdependent (600+ line) data transformations defined in SQL as a core step

Sisense For Cloud Data Teams Embeds In Salesforce

Here's an example script for how to embed dashboards in a Salesforce page - in this case, we're embedding in the Accounts page. Be sure to replace your dashboard id, API key, and include the appropriate filters

Using The Embed Refresh Trigger

The Embed Refresh Trigger PostMessage that is accepted by embedded dashboards can be used to allow public users to refresh their view of an embedded dashboard, or to set a refresh rate on those embeds. The data

Connecting To Your Warehouse Via Psql Or SQL Workbench/J

Connecting To RedshiftYou can connect to Amazon Redshift using a JDBC or ODBC connection, any tool or program which connects via these methods, or programmatically (AWS provides examples for Java and .NET). AWS

Change In KPI Chart

MotivationA frequent business question often posted is "What is the change in [users, units sold, views, etc]? Using Python's matplotlib library, one way to visualize this change is to visualize it alongside an

Custom Timezone Filter

I’ve seen this quite a few times so I thought I’d write a little community post about it since our native way in specifying timezones doesn’t work with filters! Normally, this would be the way to convert a date

Creating Charts With Broken Axes

Sometimes outliers are just in the way of chart creation and we want to put them in their own section of chart. Python can help us do that! I started with a data set like "count of users created by month" where

Difference Between Row_number(), Rank(), And Dense_rank()

Found a great article that reviews the differences between these 3 useful window functions. I've adapted the theoretical data to provide a more practical (albeit simplified) business context.All coding credit g

Getting Around Overlapping Data Labels With Python

A common hazard when adding data labels to a chart, is that often times the data labels will overlap when there are two points located relatively close to one another. Fortunately, the flexibility of python all

Custom Date Range Filter

Update: Sisense for Cloud Data Teams now allows you the ability to create additional date range filters without having to go through the below workaround. Refer to our docs here under "Additional Date Range Fil

Pivoting And Melting Dataframes

Here's the scenario: you just used SQL and have an output that shows the results you want, but it isn't optimally displayed. Instead of writing cumbersome case when statements, unions/union alls, we can quickly

Gantt Chart For Team Workflows

Gantt charts are a great way to show which projects and tasks different teams are working on at a given time. This chart type is heavily used by Project Managers, Task Managers, and anyone else managing multipl

Using Python To Write A Create Table Statement And Load A CSV Into Redshift

Usually when I need to upload a CSV I will use the Sisense for Cloud Data Team's CSV functionality. It's fast, easy, allows me to join the data with all my databases, and automatically casts types. Sometimes, h

Calculating Number Of Weekdays Between 2 Dates (Redshift And Postgres)

The parameterized snippet below can be used to find the number of weekdays between 2 dates. We named this one difference_in_weekdays(start_date,end_date) Redshift(DATEDIFF('day', [start_date], [end_date])) -(

Transposing Tables Using Python Pandas

Pandas makes transposing tables beautifully simple! Below are a couple examples using the example SQL output below:This first Python snippet allows you to define your own column headers:# SQL output is imported

Funnel Chart -- Plot.ly

Funnel charts are a great way to represent any drop-offs in sample size throughout a series of steps. Using a little bit of Python handiwork in Sisense for Cloud Data Teams' R/Python integration, we can easily