Chart x axis - Set max if less than x

[gramqlik]

Hi there,
I have 4 charts each, showing a selection of activities performed at one of four different sites. My intention was to ensure these charts are as similar as possible, for ease of comparison at a glance.

However, now that they're being populated with real live data, I have an aesthetic issue due to activities being particularly numerous at one site.

Before knowing exactly what data I'd get on a daily basis, I set a Static Max of 500 for all 4 charts. This looked fine in testing, but then one day, Activity A occurred 1,140 times at Site4, so the bar disappeared off the scale. If I increase the Static Max to, say 1,200, then the charts for the other 3 sites look terrible (especially Site2).

So, what I'm aiming to achieve is to apply an expression to the Static Max on all 4 charts that will say "If any bar on the chart is greater than 500, then allow the axis to expand, else keep the maximum at 500".

I tried writing this as an IF THEN ELSE at first, but it didn't work as desired, so adapting another thread I found on here, I tried:

= rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD')),500)

Unfortunately, this doesn't work. The axis on the Site4 chart expanded to 1,200 ok, but the axis for the Site1 chart dropped below the 500 limit (basically acting as though no Static Max had been set).

Any ideas?

A couple of associated questions:

- Each chart has 2 expressions, one showing today's activities and one showing yesterday's activities. I'm guessing that I'll need to set the Static Max formula for both (if not, and we exceeded the 500 max yesterday, presumably the chart would not expand to accomodate it)?

- Is is possible to set the format of the axis scale, e.g. turn the font red when the the scale differs from the norm (of 500)? This would just make it stand out as being a different scale to the adjacent charts.

Thanks,
G

[marcus_sommer]

Your aggr-function isn't complete - there are some parts of the set analysis, the count-field and the dimensions for aggr missing. It should rather look like:

= rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>} CountField), AggrDim)),500)

Most of color-settings (axis, title, caption, ...) could be defined by an expression and therefore you could display the color you want in this way: if(YourExpression > YourLimit, red(), back())

- Marcus

[gramqlik]

Thanks Marcus.

My chart Dimension is =IF([FlagInclude] ='1', CodeType, Null()

and the Count filed is TransLineID

So I'm trying:

     = rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), IF([FlagInclude] ='1', CodeType, Null()))),500)

for the 'Today' expression, and the same again but with ([Date Stamp])-1 for the 'Yesterday' expression.

The axis is limiting to 500 in all cases - i.e. correctly for the Site1 chart that contains only values below 500, but incorrectly for the Site4 chart, which contains counts for yesterday that exceeded 500.

[jonathandienst]

The highlighted part of the expression below needs to be a field name or comma separated list of field names. Your expression will fail so the chart will ignore your axis spec

=rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), IF([FlagInclude] ='1', CodeType, Null()))),500)

What you possibly need is:

=rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), FlagInclude, CodeType)),500)

or

=rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), FlagInclude)),500)

[gramqlik]

Thanks Jonathan. Unfortunately getting the same result with either of those amendments.

[marcus_sommer]

Like Jonathan mentioned it must be a native field(list) and not an expression. In your case I could imagine that you could just use CodeType for it and transferred the condition of FlagInclude = {1} into the set analysis. If it's not working I suggest to put this whole expression and all of it's single parts into a table-chart to check if they return your expected results. Often it's helpful to do such checks with just one and after this with a few possible dimension-values - it's easier than checking a whole dataset.

- Marcus

[jonathandienst]

Guessing a little here as you have not given much information on your data model. Did you try this?

=rangemax(max(aggr(COUNT({<TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), CodeType)),500)

Or to pick up on Marcus' suggestion

=rangemax(max(aggr(COUNT({<FlagInclude = {1}, TransLocID={'Site1'},[Date Stamp]={"$(=DATE(MAX([Date Stamp]), 'YYYY-MM-DD'))"}>}TransLineID), CodeType)),500)

Try the expressions in a text box to see what results they are producing. Break it into parts so you can see what the Max() provides as well.

[gramqlik]

Ok, it looks like Jonathan's code above is correct, but I'm still getting the wrong result.....

The reason for that is because I'm applying Static Max calculations for both expressions ('Today' and 'Yesterday'). The counts for some series' yesterday reached 555, but none of the series counts for today reach that high.

When I removed the Static Max for today, the axis expanded to accomodate yesterday's data.

So this leaves me with two questions:

1) Is it possible to apply a single Static Max calc that evaluates both expressions and sets the axis at the the higher value.

Like:

     If Count of TransLineIDs for Site1 for Today's date OR for Yesterday's date is >500, allow axis to expand, else 500.

or

    If Count of TransLineIDs for Site1 for Today's date is >500, allow axis to expand, else If Count of TansLineIDs for Site1 for Yesterday's date is >500, allow axis to expand, else 500.

2) Following the advice of you guys, I added a textbox using the calc:

     =rangemax(max(aggr(COUNT({<TransLocID={'PDPHAR'},[Date Stamp]={"$(=DATE(MAX([Date Stamp])-1, 'YYYY-MM-DD'))"}>}TransLineID), CodeType)),500)

Which gave me the result 614. I added a table object containing the relevant fields, which confirmed that the values on my charts are correct, but also that the 614 relates to a CodeType that is filtered out by the FlagInclude on the Dimension.

     =IF([FlagInclude] ='1', CodeType, Null())

So, I need to exclude the same values from the rangemax calc. If I cannot omit them using the IF statement I had previously, can I hard-code the values I do want into the calculation?

Something like:

    =rangemax(max(aggr(COUNT({<TransLocID={'PDPHAR'},[Date Stamp]={"$(=DATE(MAX([Date Stamp])-1, 'YYYY-MM-DD'))"}>}TransLineID), CodeType(TypeA, TypeB, TypeD, TypeH))),500)

Thanks!

G.

[jonathandienst · Accepted solution]

Perhaps you could do it with this:

=RangeMax(

     Max(

        Aggr(Count({<

        FlagInclude = {1},

        TransLocID={'PDPHAR'},

        [Date Stamp]={">=$(=DATE(MAX([Date Stamp])-1, 'YYYY-MM-DD'))"}

     >} TransLineID)

  , CodeType, [Date Stamp]))

, 500)

[gramqlik]

Fantastic, that's done it.

Thank you, Jonathan!