Aggregate Filter by Formula
I have an aggregated table that shows a selected company's prescriptions and compares them to the rest of the industry. The table groups by a drug category, then shows the selected company's data (# prescriptions, total cost, total sales, etc) and compares that to the rest of the industry.
When the selected company has 0 total prescriptions for a drug category, I want to suppress that row in the aggregate table. However, "Filter Aggregate Values" will not accept a "New Formula" and allow you to specify the filter formula. I can drag "Number of Prescriptions" into an aggregate filter and select only rows where SUM() > 0, but that applies to ALL the prescriptions in the row when I want to filter out rows where SUM() = 0 for the selected company only.
Is there a way to accomplish this? Why can't you use a formula on insight aggregate filters?
Mark Samuels - This feature is under consideration for a release later this fall. If the formula calculation for the aggregate filter is static (i.e. not dependent on prompt selections via presentation variables), then a new feature introduced in Release 4.7 just might help.
This feature allows you to create a business view using the Analyzer UI. For more details, refer the release notes - https://docs.incorta.com/4.7/rns/
- Create the desired logic as a formula column in a business view created using the Analyzer UI
- If possible, ensure that all other columns required on the dashboard are part of this view. At the very least, all columns required in the insight should be part of the business view, as you cannot join this view with other tables or business views.
- Next, build an insight on a dashboard and use the calculated formula in the business view as an aggregate filter in the insight
I hope this is helpful and provides an interim solution. We will also support formulas in aggregate filters soon. Thanks!
Hello - are the above solutions still the best way to use an aggregated formula as a filter? There have been a couple releases since the last comment, but I wasn't able to get it to work when I recently tried.
I used an aggregate Incorta table as Amit suggested, but it's then a two step process to add a new field to the insight.