akaplan
Sisense Employee
Joined 3 years ago
User Profile
User Widgets
Contributions
Re: Calculated Value Filter in Widget Error
Hi HQ_Dev_Prod, I reproduced this on an internal machine and found the specific error message: OVER: only possible within the selection Since this relates to Window functions, I looked into Sisense's configuration and found this translation setting: applyMeasureGrouperOptimizationForWindowFunction It appears that it's intended to optimize or safeguard what might otherwise be highly resource-intensive formulas involving window functions. Disabling the toggle alleviates the error. I searched internally for further documentation on this config and haven't found anything so far, so I can't guarantee how else this might affect your deployment.Re: Count (Boolean)
Hi BalaR, You're on the right track. Similar to your attempts, I would use a formula filter on Segment_A to accomplish this. You'll see these referred to as Measured Values in Sisense documentation. I created a sample file containing 500 participant ids and randomized Yes/No segment values, structured like this: I was able to build an indicator showing total count (and contribution %) of the records with Segment_A = "Yes" by using the following formula: (count([Participant Id]), [Segment_A]) / count([Participant Id]) The numerator pulls the total count of participant_id, with a filter on Segment_A = "Yes". You right-click the filter field (Segment_A) and select "Filter" to assign the filter member(s). The denominator pulls the total count of participant_id, without any additional filters. Let me know if this solves your issue, or if any clarification is needed (or if I've misunderstood the requirement!).Re: Filtered Measures Using Snapshots and Sisense Date Calculations
npatel_02 - how many inventory records are involved, and how many stock dates are relevant to the use case? The reason I ask is because a potential modeling solution involves pre-calculating the stock status for each product/date combination. It makes things quite straight-forward, but there are potential limitations depending on data volume / disk size. To limit the size as much as possible, the example would employ 3 tables: Date Dimension: unique list of dates, for dashboard filtering purposes Inventory: collection of products in inventory, along with dbt_valid_from and dbt_valid_to (like your attached sample data) Inventory Status: Cartesian product of date & inventory UID. This table contains Date, Inventory UID, and a calculated IsActive flag, which joins to the inventory table to check if the Date falls between dbt_valid_to and dbt_valid_from, producing a 1 or 0 for filtering purposes. Inventory Status would then serve as the key between your date dimension and inventory tables. Considerations: Size of Inventory Status table This is a supervised many-to-many, so you'd need to restrict the Date dashboard filter to single-select (not multi-select or range or anything like that). Ensure the widget(s) or dashboard are filtered to IsActive = 1 to return the desired inventory records If this approach isn't feasible, I do believe we could come up with something a bit more dynamic with a combination of scripting and/or BloX actions. But this may require a more formal engagement with our Field Engineering Services team.Re: Gradient Conditional Formatting In Pivot Tables
Hello angupta, you should be able to use the built-in Range coloring for this. You can find more information at this link. Note that you can choose between "step" and "gradient" coloring with this option. Let me know if you have any questions or do not see this as a coloring option in your pivot table(s).Re: Calculating average units for two years
Hello, you should be able to use Measured Values for this. In the widget, you can create a formula that is something like: ( DUPCOUNT([AccountId]), [Years in CreatedDate] ) And if you right-click [CreatedDate], you can apply a filter to select the appropriate year. This can be combined as needed for a larger run rate formula. ---- Alternatively, in the ElastiCube, you could create yearly custom columns like this: AccountCreated_2022 = CASE WHEN GETYEAR([CreatedDate]) = 2022 THEN 1 ELSE 0 END AccountCreated_2023 = CASE WHEN GETYEAR([CreatedDate]) = 2023 THEN 1 ELSE 0 END etc. You could then SUM() those columns within the widgets to use 2022 or 2023 counts within formulas. This logic could also be added to an import query directly, as opposed to creating custom columns. But this all depends on the exact way that your data is laid out.Using HTML for Style and Formatting in Pivots
Using HTML for Style and Formatting in Pivots Introduction A common use case within Pivot and Table widgets is to display a clickable hyperlink (see: this community post). However, the ability to render HTML within these widgets can be used for more than just links. Any HTML can be rendered, which enables a variety of styling and formatting options. In this post, we'll walk through an example project management use case in Sisense, which includes a Pivot containing project status information. In this example, each project has three health statuses: Customer, Technical, and Timeline. Each one exists as a separate column, with a widget script to color-code the statuses: Three Separate Status Columns widget.transformPivot({}, function(metadata, cell) { if (cell.value == 'Red') { cell.style = { color: 'red' } } else if (cell.value == 'Green') { cell.style = { color: 'green' } } else if (cell.value == 'Yellow') { cell.style = { color: 'yellow' } }; } ); Problem The product team has requested we combine the three statuses into a single column. Solution 1 One option is to create a dimension table in our data model that normalizes the three statuses into key/value pairs of StatusType and StatusValue, then create a concatenation of {StatusType}: {StatusValue}. The Pivot output might look something like this: Concatenated Key/Value Status Column You can see that there are two drawbacks: The Notes (and any subsequent columns) are duplicated, once for each Status We’ve lost the color coding and would need to elevate the complexity of our widget script to account for this. Solution 2 A second option, which is the subject of this article, is to use a combination of SQL and HTML to produce the combined Status column, which eliminates the duplication issue above (#1) and arguably makes our styling implementation much simpler (#2). Rendered HTML Status Column Here is an example of the SQL/HTML within the data model's custom column, which produces the HTML that is rendered in the Pivot. '<b><p style="font-size:12px">Customer: ' + CASE WHEN CustomerStatus LIKE 'Red%' THEN '<font color="red">' WHEN CustomerStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN CustomerStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = #black' END + CustomerStatus + '</font><br>' + 'Technical: ' + CASE WHEN TechnicalStatus LIKE 'Red%' THEN '<font color="red">' WHEN TechnicalStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN TechnicalStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = black' END + TechnicalStatus + '</font><br>' + 'Timeline: ' + CASE WHEN TimelineStatus LIKE 'Red%' THEN '<font color="red">' WHEN TimelineStatus LIKE 'Yellow%' THEN '<font color="yellow">' WHEN TimelineStatus LIKE 'Green%' THEN '<font color="green">' ELSE '<font color = black' END + TimelineStatus + '</font></p></b>' Reminder: The configuration option Allows rendering Pivot Table content as HTML must be enabled within the Admin menu. The link at the beginning of this post describes the process in more detail. Keep the size of the table and text field in mind, to avoid negatively impacting build or query performance. This is just one small example of employing HTML for a stylistic use case, as opposed to a clickable hyperlink.
