0

Aggregation setting to exclude non-null and/or non-zero values

I'm setting some measures to AVERAGE in a dashboard, but what I'd really like is an average of the non-zero values  ( in other cases non-null values ).    Is there such a setting?

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • sum(if(Schema.Table.Column > 0.0, Schema.Table.Column, 0.0)) / (sum(if(Schema.Table.Column > 0.0, 1, 0)))

    Note: I didn't syntax check this yet.

    Like 1
  • Thanks Dan - I'll give this a go today!

    Is there any "behind the scenes" difference between putting a calc like this in an insight v. a business view?   I think I recall both should execute at runtime?

    Like
  • Correct.  Formulas at the dashboard and business view levels execute at runtime.  In the physical schema, they calculate at load time.

    Like
Like Follow
  • 2 wk agoLast active
  • 3Replies
  • 11Views
  • 2 Following

Product Announcement

Incorta 5 is now Generally Available