Traffic Light Gauge - Unbounded Min/Max

[JustinDallas]

Hello Everyone,

I am using the Traffic Light Gauge(TLG) in a chart of mine.  It basically compares the percent totals for sales as it's expression => SUM(thisYearsSales) / SUM(lastYearsSales).  Clearly, this is an unbounded formula with Infinity being the upper bound, and negative Infinity being the lower bound since the business can actually have negative sales.  Unfortunately, when looking at the TLG Gauge Settings, I don't really  have a way to convey that with just Min/Max settings unless Qlikview has an +Infin/-Infin keyword that I don't know about.

I've tried using the math.min function, but then Qlikview complains about nested aggregation.

Any help on this matter is greatly appreciated.

[b_garside]

You need to use the Aggr() function.

The outer aggregation can be anything you want Max/Min/Sum.

Sum( Aggr( SUM(thisYearsSales) / SUM(lastYearsSales) , Dimension))

[JustinDallas]

Hello Brian,

I've tried to implement your solution, but I can't wrap my head around how to make it work.  My original expression is

=SUM({<Year={$(=Max(Year))}>}NetSalesDOM) / SUM({<Year={$(=Max(Year)-1)},CurPrevYTD={1}>}NetSalesDOM)\

I'm not sure what goes in the "Dimension" field of your solution. 

[b_garside]

If I understand what your trying to do to I think you may need the Aggr() function to gather or group all your percentages so then you can find a single Max/Min value that's dynamic.

The Dimension field is where you place the field you wish to group the summation by. Think of Aggr as a grouping function that creates a virtual table which then can be aggregated upon by any other aggregation function it usually wrapped by another to get a single value.

There is a good blog post on this. I use it a lot when I need to use max and it needs to be used to find the upper bound of a gauge when a single Max function wont do it.

AGGR...

https://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-...

QlikView Technical Brief - AGGR.docx

Most places in QlikView demand that you write your expression so that it returns one single value. This means that youmust wrap the Aggr function in an aggregation function to get a meaningful result. The only exception is if you use the Aggr function to define a calculated dimension or field. This means that you have two aggregation steps; one nested in the other:

         Avg( Aggr( Sum( Amount ), Month ) )

[b_garside]

Drillnaut,

Did the info above help you determine if AGGR will help?

Mark as Helpful or Answered if it answered your question.

Thanks,

Brian

[JustinDallas · Accepted solution]

The solution I ended up implementing was a lower bound of -1.1 and an upper bound of 10000000.  Since this is percentage of sales, the chance of that value falling out of those bounds is pretty nonexistent.