Using formula to specify range for y axis

[JediBabe]

Hi,

I have a line graph in an app where the auto range for the y-axis isn't calculating correctly (it goes up to 60m, whereas it needs to only go up to about 7m).  I have tried to create a custom range but any formula I have used hasn't given me what I need so at the moment it is hard-coded at 7m.  Can anyone help?

For reference, the graph is showing the numbers of different failures types, so there are multiple lines.  I attempted the following formulae:

 =RangeMax(max(broken_zip_count),count(duplicated_quotes),max(invalid_pk_integrity_count),max(invalid_xml_count),max(skipped_xml_count))

This came out far too low (note that duplicated_quotes needs to be different).  I then tried:

=RangeMax(sum(broken_zip_count),count(duplicated_quotes),sum(invalid_pk_integrity_count),sum(invalid_xml_count),sum(skipped_xml_count))

This came out far too high.  Where am I going wrong?  

[Lauri]

I am guessing you have a dimension on the X axis, and your formula for the range covers all of that dimension's values, so using sum (in your second formula) made the max too big. 

If your data model has values for broken_zip_count for each day, and your chart summarizes by month or year, then you need to find max(sum(broken_zip_count)) for each month or year.  And then adjust the Y axis range accordingly. 

You could do that in your load script, or using Aggr (which could degrade performance if you have a lot of data):

Max(Aggr(Sum(broken_zip_count), [timeperiod]))

Finally, your Y axis range formula becomes:

=RangeMax(Max(Aggr(Sum(broken_zip_count), [timeperiod])), Max(Aggr(count(duplicated_quotes), [timeperiod])), max(Aggr(sum(invalid_pk_integrity_count), [timeperiod])), max(Aggr(sum(invalid_xml_count), [timeperiod])), max(Aggr(sum(skipped_xml_count), [timeperiod])))

Of course, adjust the inner function to whatever is most logical for that field. I assume Sum everywhere except on duplicated_quotes.