on - edited on by
DRay
Many times we’d like to be able to compare a few items out of a large set to see how they relatively behave. For example, how a certain period did compared to any other period, or how a certain country’s sales or sales representative achieved compared to another. Another feasible option for this would be a financial Trial Balance report in which we’d like to compare several different time periods for beginning balance, ending balance, credit and debit, all to be affected by different months selected from the filter, all in one single pivot table or widget, or across several widgets.
This can be achieved by use of the parameters plugin though this requires one to add an additional filter for the same dimension as the number of items you’d like to compare through, i.e. if you’d like to compare 2 date periods or 2 countries, you’ll need 2 different filters, for 3 values or above this can get unmanageable. With this method, one can use one single filter for all items being compared.
To achieve this functionality we will be using a combination of custom fields in the EC, measured values, ranking filters and the smart label plugin.
To start, we will create a custom column for the dimension we’d like to dynamically filter, in our example we will use a date dimension, though this logic can be applied on textual values and others. This custom column will hold a unique incremental integer value for each day/quarter/month/year according to what we’d like to compare, in our case we will use months. The best way to reach a unique incremental integer for date elements would be a unique integer representation for each month, in our case we will calculate the number of months which had past since 1/1/1970, a sort of Unix timestamp for months:
This way, when grouped by months, every date will be allocated a value which represents the number of months which have past since the 1st of that month.
Note: To achieve this functionality on other textual valued dimensions, in the custom column simply create a ranking value which will grant a unique incremental integer for each value, this can be found in the Math functions in the custom elements function references.
Once these values are ready in the dimension table, we can move forward to Sisense web. Basically, in each widget or value you’d like to present these various values, we’ll apply a measured value which will be filtered to a top or bottom ranking value of the custom unique incremental integer value we allocated for each dimension value. To do this, create the measured value, in the filter section as follows, apply the filter:
For most recent month selected, we will apply the top ranking, as the most recent month will have a larger int value which we allocated in the custom column, as more months have past since 1/1/1970, for both filters, we will apply the ranking filter according to Max of the same Int value:
For the first month selected we will apply the same measured value, this time for the bottom 1, i.e. the lower value of the two. For both filters, we will apply the ranking filter according to Max of the same Int value.
Another option in order to improve user experience to better understand which month (or any other value) is represented in each value, one can use the Smart Label widget to show on top of each value, which is the month currently represented, in the filter label widgets we will apply the same filter, at the widget level, of top and bottom ranking according to our unique Incremental integer value respectively:
Attached are .dash and .ecdata files via google drive, would appreciate any feedback on this, please do let me know if I can further clarify or assist on any of the above.
