Conditional 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.Top 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: DownloadRanking range
Introduction Many times we'd like to analyze our data according to top/bottom ranking positions, for this we have out-of-the-box ranking filters. At other times, we'd like to see certain ranges, such as the ranking range between certain ranks, i.e. positions 5-10 or 11-50. Our standard top/bottom ranking filters will not suffice for this analysis. Example In this example, we'd like to focus our attention on our product categories ranking 4-6 to see how to improve our medium-selling items. For this, we'll use a combination of the RANK() function (https://docs.sisense.com/win/SisenseWin/function-references.htm) and then determine which ranking indexes we'd like to display: Steps 1. Regardless of the ultimate widget you'd like to use, tart with a pivot table to see all the categories and actual data. 2. Add the RANK() function, the first argument within this function should be the measure according to which you'd like to apply the ranking, in our case SUM(Quantity). The second argument is the manner in which we'd like to arrange our ranking, "ASC"/"DESC" in our case we'll use "DESC": This will create the Categories' indexing according to our top ranking. 3. We will now apply a filter by value on our newly created RANK function, use the range which you'd like to display, in our case, we'd like to show the ranking values that are between 4-6: That's it! you will now be able to show the specific ranking range required, you can even go ahead and disable the RANK function toggle switch so it will not be displayed within the widget.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.dashCalculating 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.ecdataPerforming 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 SQLForecasting 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.ecdataPerforming 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)Inclusive and Exclusive filter conditions - Filtered Measure Solution
The following article describes how to answer the following question: Which countries have sales for product A but not for product B The solution is based on using the Filtered Measure plugin. 1. Go over the instructions in the Technical Details of the plugin. Please follow steps 1-8. 2. After understanding how to work with the plugin, In our example, we would like to know what is the Cost for countries that had sales in particular Categories but didn't have sales in other categories. Here are the selected fields for this business question: In the EC we will duplicate the Category field On the dashboard we will create 2 filters out of both Category fields - Include Category and Exclude Category Add a new pivot table and add 2 Rows: Country and CountryID * The countryID field is presented only for QA purposes 3. We will create 4 Values in the Pivot table: * After creating values #1 and #2 save them as Starred Formula to be then used in value #3 4. Filter Value #3 to include only values = 1 5. Hide the first 3 values and leave only value #4 visible 6. We can now interact with the two filters on the dashboard level and see how the countries presented are changing accordingly. If we were to add other filters, for example: Date year = 2013, we would be answering the business question of: What was the Cost in 2013 for Countries that in 2013 had costs for product A but not for product B The SQL equivalent of this business question is: select c.[Country ID], sum(c.[Cost]) [Total Cost] from [Commerce] c where c.[Country ID] in (select c.[Country ID] from [Commerce] c join [Category] ca on c.[Category ID]=ca.[Category ID] where ca.[Category]='Apple Mac Desktops' and getyear(c.[Date])=2013 group by c.[Country ID]) and c.[Country ID] not in (select c.[Country ID] from [Commerce] c join [Category] ca on c.[Category ID]=ca.[Category ID] where ca.[Category]='Camcorders' and getyear(c.[Date])=2013 group by c.[Country ID]) and getyear(c.[Date])=2013 group by c.[Country ID] That's it!Show last month with data
The last month with data isn't the current calendar month (payroll for instance).The out of the box feature of Sisense supports only the last calendar month Below shows a solution to have a default filter that shows the most updated month with data. Solution: Add a custom table to the Elasticube that holds the months order, this table will have two fields: MonthKey and order, one row for each month Example of Month_Order custom table script: SELECT MonthKey, rankdesc(MonthKey) MonthsOrder FROM (SELECT DISTINCT MonthKey FROM [fact1] ) m 2. Create a custom table for time dimension (which can use the CSV file as a source) and add a field that indicates what is the month order for each day SELECT Date,dc.monthkey,monthsorder FROM (SELECT Date,100*getyear(Date)+getmonth(Date) monthkey FROM [Date Dimension.csv] WHERE Date > createdate(2008,1,1)) dc --restricts only dates since 2008 JOIN [Months_Order] mo ON mo.MonthKey=dc.MonthKey Elasticube screenshot: 3. In the dashboard use the time dimension field you added in order to show the relevant months For example: Use it as a dashboard filler in order to show last month of data: set MonthOrder as 1 (you can set it as background filter) Use it as a widget filler in order to show last month of data compared with previous month with data: