RangeMin Question

[DimSum]

Hello,

I'm attempting to build a dynamic range on a chart to reflect the minimum of a value for months that have occurred thus far.  I've attempted to use rangemin but haven't cracked the code as what I've attempted includes future months & thus skewing the result.  From the example data below, what I'm hoping to return as a value is (35.8%) not (100.0%) as we're currently in the 4th month of the year.

Any help is appreciated.  Thank you.

Month	Variance
1	19.4%
2	28.6%
3	(35.8)%
4	.4%
5	(100.0)%
6	(100.0)%
7	(100.0)%
8	(100.0)%
9	(100.0)%
10	(100.0)%
11	(100.0)%
12	(100.0)%

[sandeep-singh]

I believe the value will be 0.4% not 35.8 as you are in 4th month of the year. try this

=Min({$<Month={"<=$(=Max(Month))"}>} Variance)

[DimSum]

Thanks Sandeep but unfortunately, that isn't working.  The Variance field in the example is a measure not a dimension which appear to be why it's not working.

To clarify why I'm expecting (35.8%) as a result rather than .4% is it's really -35.8%, just displayed with parenthesis rather than a negative sign.

[stevedark]

Hi @DimSum 

Where are you wanting the value to appear, is it a KPI or a separate column in the same table?

It is likely that you are wanting to use the aggr function, this creates a virtual table that you can then do another expression over, so you would want something like:

=min(aggr(if(Month <= Month(today()), sum(Value1) / sum(Value2)), Month))

Be careful with using Month on it's own without years, as the functionality will not work if you span the end of the year. I would use the MonthStart function, rather than Month, to avoid future problems even if I only had one year of data at the time.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[DimSum]

Thanks for the reply @stevedark - I'd done some brief investigation into using aggr was struggling with it.

To provide additional insight into how I want to use the value, it's for the range of the secondary y-axis of a combo chart.  Within that chart the primary axis y axis contains measures based on the current & prior years, the x axis contains dimensions & alternative dimensions while the secondary y-axis contains a % variance between the primary axis measures.

I've included an example of the chart here where I'd like the range to be based off the minimum variance % of the months with current year values, in this case the 4th month's -35.8%, as the minimum for the secondary y-axis range rather than it currently using -100% from months 5 - 12 being they do not contain current year values yet.

[stevedark · Accepted solution]

Hi @DimSum 

The statement I put above should work then, I think.

Try putting it into a KPI or the title of a chart to test it before using it as the RangeMin.

=min(aggr(if(Month <= Month(today()), sum(Value1) / sum(Value2)), Month))

The Month field, in orange, must exactly match the field that you have as a dimension on your chart. The expression, in blue, needs to be the expression which is driving your black bars in the chart.

The other way of achieving what you are after may be to remove the future values from the expression for the second axis. Sense should then auto fit the axis correctly. That may look a bit like this:

sum({<Month*={"<=$(month(today()))"}>}Value1) / sum({<Month*={"<=$(month(today()))"}>}Value2)

Hope that helps.

Steve

[DimSum]

Thank you so much @stevedark!  The solution you'd provided in your original post worked - it didn't sink in for me until either your explanation in your second post or I'd more time to absorb it - thanks again!

[stevedark]

Thanks @DimSum - glad it's working for you.