Year over Year for the past x months
Analytical Need Sometimes we wish to see how we are performing in the last x months and comparing that to the previous parallel period. For example, we wish to see how we perform from Sep last year to Aug this year and compare it with Sep 2 years ago to Aug last year. Modeling Challenge If we use the period-over-period mechanism, we will have the period number (even if displayed by text) ordered from the first one, not necessarily ending in the current period. Solution In our example we present a view of 12 months back. We will create the widget with the following definitions: 1. The x axis is the month (out of the date field) 2. We will filter the x axis for the past 12 months like so: Pick Time Frame and then 2 months. Image 1. Date filter with 2 months selection Click on Advanced and change the count to 12 and offset to 0 : Image 2. Date filter with 12 months selection (including this month) Click OK. 3. Add 2 values: This Year - sum([Value]) - the regular formula you wish to calculate Last Year - (sum([Value]), prev([Months in date],12)) - same formula but with a measured value that uses the PREV function which gives you the option to take 12 months back for each of the dimension (Month) values. You will get this widget: Image 3. YoY but with a specific x Axis But the x axis is misleading because of the year. So we would want to change it to reflect the month name like so: Image 4. Formatting the x axis labels And now the X axis will look like this: Image 5. Final Widget 12 Months YoY Cube.ecdata test12monthsYOY.dashPerforming An Aggregate Product In Sisense
Purpose: To create a product aggregation in Sisense (multiplies all values together, similar to the way a sum function adds all values together). Situation: A financial use case has a table with relative daily gains/losses for certain securities (think stocks; if yesterday it closed at $100 and today it closed at $101, then there was a 1% gain today). The goal is to calculate the % change over any given timeframe from the start to the end or in other words, multiply each day’s value (+ 1), or in Excel land Product(1+value). Approach: There is no product function in Sisense (it is not a common BI function), so we needed to break down the function into its fundamental parts. The end result, which assumes we have all positive values (applicable for this use case) looks like this in SQL: Note: the base of the log must match the base of the exponential Resolution: Start with the table shown below. The XLE column is the relative gain or loss for that day and the XLENorm = XLE + 1 (now they will all be positive). The idea here is to take the product or in other words multiply all values form the XLENorm field together to get an aggregate relative value over any given date range. 2. Create a new widget with the following function. 3. This is similar to what was shown above, but the LN() function takes an aggregation. Therefore we make the input for LN the sum of XLENorm, and in the SUM() we add a multi-pass by date. It will roll up to whatever you like however I suggest duplicating the Date field so you can drill all the way down to the dates as well (requirement of multi-pass). 4. Finished product: Notes & Insight: This could have several potential applications for financial or other use cases (compounding interest rates, etc.) As noted before this particular calculation assumes a positive value which was fine for our purposes. As outlined in the document there are some other more encompassing formulas which will work for zero, null and negative values. These should be possible in Sisense but would require some nested conditional statements or perhaps some smart use of filters, absolute values, counts of negatives (aka the product of an even number of non-null or zero values is positive, and the product of an odd number of non-null or zero values is negative) and possible Boolean algebra A product function is not available in most other BI tools, nor as a standalone function in SQLPerforming an OR between dashboard filters
Analytical Need A common requirement in data analysis is to be able select values from several filters and to see the results that contains all of those values and not only the results where only selected values appear. In fact, the requirement is to have an OR and not an AND between the filters. For example, I have 2 filters of country & product. I choose country "UK" and product "A": AND will get me results just for product "A" sold in the UK OR will get me results for all the products sold in the UK or all the countries that product "A" has been sold in. Challenge The default behaviour of the filters in a dashboard is AND. We need to find a way to turn this into an OR. Solution We will use the filtered measure plugin to display the OR result. This add-on is pre-installed on Sisense in Linux environments and its version could be different. The download link is for Sisense on Windows. Our data: When we select product A and country UK we don't want to see the second record (B, Greece), but we do want to see all the rest. Dashboard filters: In order to get the desired result we will define the following formula: (sum([value]),[@Country])+(sum([value]),[@Product]) The @ sign means that this part of the formula refers only to the mentioned dashboard filter. The + sign is actually a sum between the values. However, this is only getting us half way, since for the records that contain both UK and product A - we will get the amount doubled like so: We need to divide these result by the amount of filter values the record is associated to. In this case, we need to divide the first row by 2 (because we have UK & A) and the rest by 1 (we have either UK or A). In order to achieve this calculation we will need to use this formula: (([# of unique Country],[@Country])+ ([# of unique Product],[@Product])) So the final formula will be the division between the 2: ((sum([value]), [@Country]) + (sum([value]), [@Product])) / (([# of unique Country], [@Country])+ ([# of unique Product], [@Product])) Now the results are correct: In case you have more than 2 filters, you'll need to add more calculations to the nominator & the denominator. Note: the rest of the dashboard filters that don't participate in the formulas will behave with the normal AND functionality. Cube Detailed calculations The default behavior (no filter measure)Calculating Weighted Average
Analytical Need Weighted average refers to the mathematical practice of adjusting the components of an average to reflect the importance of certain characteristics. This practice can be useful in different scenarios, a few examples: In economics, this can be used to calculate the price of a stock when each stockholder has different shares with a different price. In academics, this can be used to calculate a student's grade based on exams with different importance. In marketing, this can be used to calculate the average of a product according to the distribution of the product with its price in different stores. Modeling Challenge In addition to the mathematical aspect, in many cases the relevant information for the calculation would spread over different tables. In the discussed example, we will look at several products and calculate their average price. A product is sold in several stores; Different stores have a different price for the same product; A store's distribution varies. Considering that, using the simple Average function would not be enough. We need to take into account how many times each price appears and calculate the average price accordingly. Looking at a small sample data set, the structure would be similar to this: The .ecdata and .dash files discussed in the example are attached for your convenience. Solution This requirement can be achieved by creating a custom formula in the dashboard. The logic would be: Sum of ( Price * Number of countries per store ) / Sum of ( number of countries per stores ) The formula would be written as follows: To understand the formula's components, we can use a pivot widget to split it. For better clarification, we will start by looking at all the relevant fields for the calculation: Set one field for the maximum price. The purpose is to display a single value. In case you have the price for a product in a store listed more than once, setting it to total would sum the values and return the wrong results. You can also use minimum or average instead of maximum. For more information, please review this article regarding multi-pass aggregation. Add a formula for max price * # of countries – The next step would be to sum the results and group it by the product. In the final display, we will not have the store_id to be used for grouping. So, we will calculate the results by stores, sum them up, and group it by product, which is used as a dimension in the pivot: To calculate the denominator, we need to get the total number of stores, in all countries, so we can count the appearances of a specific product. We will do so by counting all countries, per store, as follows: After disabling the store_id dimension and applying the formula: After combining the entire formula: To Demonstrate the difference between the weighted average and a standard average: Attachments WeightedAverage.dash Weighted Average.ecdataTop 5 + Bottom 5 In The Same Chart
Analytical Need You want to display a chart/table with the Top X and Bottom X, all in the same widget Modeling Challenge Sisense allows setting ranking filters based on a measure, but this is only for Top OR Bottom. Solution Widget Filter: Create your widget, with the value you want to rank by (for ex. sum( [Visits] ) ). Create the rank filter measure Start with the RANK function to rank the values DESC Wrap the RANK function with an IF statement, saying IF (Rank <= X , 1, 0). This will return a value of 1 if the member is in the top X Repeat this, but switch the RANK to a DESC in order to get a value of 1 if the member is in the bottom X Add these two IF statements together, which will give you a value of 1 for Top X members and the Bottom X members Create a widget filter from your formula, that filters where the value = 1 (Optional) Include the ranking: If you just try to add your RANK function to the widget, it won't work. This is because the values in the widget are sliced by the widget filter down to just the Top/Bottom X, and then the RANK is calculated (which means the RANK will just show values of 1-10). Create the true rank formula Start with the RANK function to rank the values DESC, and wrap it within an IF statement If the rank <= X, then just display the rank else, count the unique values of the dimension and subtract the rank ASC + 1 Example Dashboard: DownloadConditional Filtering on Measured Values
Analytical Need Aggregation functions are used to group a set of values to perform calculations that return a single value. SUM(), MEDIAN(), and COUNT() functions are some of the most common aggregation functions examples. Simple aggregation functions require only a numeric field to perform the aggregation upon: Function (<Numeric Field>). Multi-pass is a type of an aggregation function which performs an additional calculation over the data and group it by a specific field: Function (<Group By>, <Aggregation>). More information about using multi-pass can be found on this link. Certain business needs, such as performing conditional calculation by group in a pivot table while keeping and allowing flexibility in the front end might require more advanced calculations. Therefore, we would like to do the calculation on the dashboard level rather than in the ElastiCube. Modeling Challenge How do we perform grouping in the dashboard while keeping the ElastiCube flexibility Let's assume that a Sales Manager would like to answer the following business question: Restaurants are sorted into 3 categorizes by number of customer visits; 0-100 customers, 100-200 customer or 200+ customers. What is the total # of customer visits each category of restaurant receives? Solution First, it is crucial to break down the question into components: Count # total customers. Group by restaurants. Filter by groups. As there are several filters over the same value, instead of creating a filter on a widget level, we will create a filter over the measured value for each group. Let's start by grouping our values and creating a new predefined value for each group. In our example, Group 1:0-100 customers, Group 2: 100-200 customers, and so on. Then we will use multi-pass aggregation to calculate the specific group by the relevant filter. Let's break it down to understand how to create the correct formula that fits for this scenario: 1) To answer the need, we will use a CASE WHEN statement to create a range of # customerID to be between 0-100 customers which we would like to eventually sum. We would like to start with examining the filter first, to make sure we are filtering out the correct data. To do so, we will add a new value and create a formula by pressing on 'fx'. We will add the following formula: Essentially, return the # customers, if the # customers is in the predefined range, else return 0. To verify the correctness of the filter, let us verify the data: We can see that when the total # of customers of a restaurant are within our filter's range, the total is added to the 'Formula' column and when it isn't in range, the column will get the value of 0. 2) The next step will be to add the multi-pass function in order to group the results by Restaurant and sum all results. This will sum all the values already filtered for all restaurants, including ones that received a value of 0 from the initial filter. This will lead to the correct result which is summing up only the # customers for 0-100 restaurants. Here's the final query: Here's the pivot now: As the grouping duplicates the data for each row, we would remove the RestaurantID row, and get the final result of the data: 3) Apply the same logic with only changing the range for the rest of the group in a different measured value. Summary: The CASE WHEN statement performs the filter into groups and for every RestaurantID that its total amount of unique CustomerID is between 0-100, we return the # of unique customers. If the total amount is not in range, we count nothing.Calculate and compare selected member to non-selected members
Analytical Need You want to display a value for a selected member (i.e. Salesperson) vs the min/max of all non-selected members. This can be achieved using modeling techniques but this solutions describes how to do it purely in the front end. Solution Here is a pivot table showing the count of patient visits per doctor month by month. We are filtering the dashboard to a specific doctor, Jermaine. The question we want to answer is "What value is the best of the rest?" Meaning, based on the selected member, what is the max of the non-selected members in each month? There are three possible outcomes: The selected member is not the max. In this case, the max of the rest = the max of all. The selected member is the max, and the there is no tie for the max. In this case, the max of the rest = the second highest result. The selected member is the max and is tied with at least one other member. In this case, the max of the rest = the max of all. We use the Filtered Measure certified add-on to accomplish this so make sure to download and enable it! Let's use a pivot to validate our results. Create a pivot that is broken out by month and dimension you plan to filter on and add a value with the calculation, in this case a simple count of patients: Note the max in each month. Add another value with a formula to calculate the rank within the month: Add a third value like the following: This is checking IF the rank of the row is 1 AND the count of the selected member (using the @ to reference the dashboard filter, per the Filtered Measure plugin) is equal to the max of the count of all members, then set the value to -1. ELSE set the value to the count of the row. Here are some results: The selected doctor is Jermaine. In 07/2011, he is tied for the max, so we set one of these values to -1 so that it is no longer the max. In 08/2011, he is not the max, so we just return the row value. In 08/2012, he is the max, so we set it to -1. Add a final value (the above value was saved as a formula for legibility): This means get the max of all the above values. After removing the group by on Full Name, here are the results: Remove the grouping by member, and change your widget into the desired final form, such as a line chart showing the selected value and the max of the rest: Nice!AND filter - How to identify Items that have all selected values
Analytical Need I would like to count all the products that are sold in Germany & Canada. When you select Germany and Canada in a typical Sisense filter, it will give you results for products that are sold either in Germany or in Canada but not sold only in both countries. Modeling Challenge In order to achieve this, you may need to perform a self join in the elasticube. If you have several combinations of attributes that you want analyse then you need to have many many tables which is not scalable or maintainable. The preferred way to do it is in the dashboard. Solution This is our example data: Image 1. The data In the "country" filter we choose Germany & Canada and we want to get 2 (just id 1 & 2. They share Germany & Canada). 3 & 4 have just one of the countries and we don't want to count them. Image 2. Filter Selection We present here 2 widget types that have the formula to calculate it but you can take the formula and apply it to other widget types. 1. Pivot: Image 3. Pivot We have 3 measures here: # Records dupcount([id]) Give me the number of ids in the data, according to the filter selected # Ids that have the Country filter values case when dupcount([id]) >= (count([Country]), all([id])) then 1 else 0 end Here we check whether the number of ids is equal or higher than the amount of values selected in the filter (we add the all(id) because we don't want the rows to affect the count of Country). if so, then mark it as 1 otherwise mark it as zero. Selected values in Country (count([Country]), all([id])) Give me the number of values selected in the Country filter, regardless of the ids (in the pivot it automatically breaks it down by the rows and here we want to ignore it). 2. Indicator: (this is the formula you should use for other widget types) Image 4. Indicator # Ids that have the Country filter values: sum([id] , case when dupcount([id]) >=(count([Country]), all([id])) then 1 else 0 end) This is the same as the formula in the pivot, just with a multipass aggregation over all ids. This is because we want to sum up all the 1s for each id. Download: Dashboard EcdataForecasting by Historical Performance
Analytical Need While tracking your sales performances during the quarter and compare it with the quarterly target, wouldn’t be helpful if you’ll have an indication for your current pace and whether you’re going to meet your quota by the end of the quarter? This article will suggest an approach to forecasting it by using last year(s) pace. Solution Prerequisite: having a daily target. You can use quarterly target and divide it by the count of total days to having for each day the relative portion of it. The idea is to take historical performance* and compare its pace to the current. To do it we'll want to calculate the expected pace, where we should be at some point of time during the quarter, in terms of % of the quota according to last year (LY) pace during the same time. The formula would look like this: For example, let’s assume that for this Q (Q1) the target is $100M, LY sales during the entire Q1 were $75M and the sales for Q1 LY till Feb 28 th (1/1/17-2/28/17) were $50M, so the Pace for 2/28/18 is expected to be $66.67M: Let’s assume that today’s sales (QTD for 1/1-2/28 2018) are $60M. it means that according to LY pace we’re $6.67M behind of where we need to be as of Feb 28 th . It means that if the sales team won’t do anything about it (and continue to work according to the same pace of last year) most chances they will miss their quota. Here is an example of a dashboard which describes this case from few different angles: From top left to bottom right, we can learn about: Current quarter sales performance (and compare it with LY QTD performance). note: It's a good example where the total sales year over year (YoY) is greater, but comparing to the quarterly target or to the desired pace it's not good enough. How far we got in terms of % of the quarterly target: and compare it to where we should be using the following formula: The current gap we have by comparing the actual to the expected pace: The estimated $ amount we should close the quarter with and the expected delta from the quarterly target: How did the gap between actual plan build during the quarter (using running sum (RSUM) for both the Actual and Target). And finally, a visual view of where we are (green area), where we should be at this point of time (black line) and what we left to achieve by the end of the quarter (blue area). Attached are the dash and ecdata files. * In this example we took last year performance, but it can also be the average of the last 3 years, the weighted average of the last 5 years, the median of the last 6 quarters or any other approach you can think of and make the most sense for your use case. Attachments - 589 KB - ForecastingbyLYPace.dash - 671 KB - Forecast by LY Pace.ecdataPareto Analysis
Analytical Need Pareto analysis is a technique based on the Pareto Principle which states that 80 percent of problems or effects are produced by 20 percent of causes. This type of analysis can be useful in the stage of decision making, to identify the root causes (or attributes) of the problem you are trying to address. Modeling Challenge How can we identify the customers that are responsible for 80% of total sales ? We can answer this business question flexibly in the dashboard. Solution To answer this question we will need to use a column chart with the relevant category we would like to analyze (customers) and add more values (running contribution and sales ) to it, to better visualize the answer to our question. Next, add the calculation of the value that needs to be measured. We will use total sales for this scenario: Our next step will be to add a value which will contain the running contribution of the total sales formula we previously created, wrapped by a running sum function, to sum up the percentage of sales per customer: Finally, we add a value line to represent the 80%: Our final result will present a column chart of total sales by customer, along with a running sum and 80% values to visualize the Pareto analysis. This enables us to quickly identify the point where the contribution and running sum values overlap, to create the 80%: