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.dashCalculate 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!Limit Dashboard Filter Items By Another Filter
At times, you might want to limit the items suggested in a dashboard filter by another field's value (for example: enable the user to choose between all subcategories under a specific Product Category). This functionality can be achieved using a dependent filter and limiting the parent filter using a background filter However, when there is only 1 value allowed for the user, the parent's selection of a single value only might seem odd. Another way to limit the filter could be by using a single filter and applying an advanced filter over it. Steps to Creation 1. Add a new dashboard filter over the field that the users should be able to choose from (In our example, Product.ProductSubcategory. 2. Open the Advanced Filters Tab, and replace its content with the following { "attributes": [ { "dim": "[Product.productCategory]", "filter": { "members": [ "Bikes" ] } } ], "custom": true } The dim attribute should be set to "[table.column]" (including the brackets), and the members should include a list of all values in the internal filter. 3. Set the new advanced filter as a background filter