0

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?

12replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Dan, we do not support aggregate filter by formula yet although it is on the roadmap. However, we may have a workaround. Let's follow up on zoom call to make sure we have the use case correct and test the workaround. We can update with our results here.

    Thanks,

    Dustin

    Like
  • Has this feature been implemented yet and/or do you have a viable workaround?

    Like
    • Joe Steinbrunner we did not have a workaround in Dan's use case. Sometimes you can get around this by creating an alias to your transactional table, self-joining and removing null rows with an insight filter. The feature to support aggregate filters is being considered for our next release but we do not have final commitment and an ETA. The next release is adding the engine support necessary to achieve the feature so it will definitely be soon. I'm already tracking this closely for Inmar and will keep the team in the loop on this status.

      Thanks,
      Dustin

      Like 1
    • Dustin Basil 
      Will this post be updated when you have an ETA or should I create a ticket for this same functionality?

      Like
    • Laurentiu Pteanc It would be best to create a ticket to more closely track this for you as well as keep you in the loop w/r/t feature requirements and implementation details. Thanks!

      Like
  • Has this functionality been addressed? It is a large stumbling block in creating insights that can identify exceptions by calculated measures. Particularly percentages.

    Like
  • 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!

    Like
  • Dan Nielsen  - One workaround for now is to first create a aggregate Incorta table and then create a dashboard on that Incorta table and use a regular filter to filter the row with 0 prescriptions.

    Like
    • Amit Kothari Hi Amit, I have never built an aggregated table. Is this also susceptible to Presentation Variable results as Dan mentioned above? 

      Like
    • Sorry meant the presentation that Ashwin, not Dan, mentioned above.

      Like
    • Mark Samuels  The business view created using the Analyzer UI also accepts Presentation Variables

      Like
  • Hi Amit,  Is that functionality available in 4.3? Sorry, again I have never built an aggregated table. Is there documentation on this?

    Like
Like Follow
  • 4 mths agoLast active
  • 12Replies
  • 244Views
  • 7 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!