Friday, August 8, 2014

The Worst Expression Ever

I wrote this as a calculated dimension for a client today.


Yes you are reading that right...

  • Five expressions with Aggr.
  • Set analysis with an aggregation search
  • Five Class sets.

So you might be wondering what I am doing and why I chose this approach.

This expression creates irregular buckets for grouping medical claims by the total amount paid. The buckets go like this
<$10,000
<$25,000
<$150,000 (with $25,000 buckets)
<$300,000 (with $50,000 buckets)
>$300,000

So the chart ended up looking like this.



Normally I would add an interval table which would have preset buckets but the problem I ran into with this is that I would have had to pre-Aggregate the claims since the qualifier is based on the total claim amount not each individual line.

So I am defining variations of the Class function and using set analysis to limit which claims will produce a value for each bucket. then using the RangeMinString to choose the bucket that is not null.

As I said before I know this is an evil expression so please post suggestions in the comments and we'll see if we can come up with something that will perform better and meet the requirement.

I know there would be worse ways to do this with If() but I thought this approach was unique and might help somebody in a similar situation.


Happy Qlikking

No comments:

Post a Comment