Problem with background colour of bar chart to show min and max bar as exceptions

[#1]

Hello there,

I have a bar chart with fiscal quarters along the x- axis, with the value being a distinct count of order numbers. I am trying to work out how to change the background colour of the largest bar.

Please can some one help me understand what is wrong with the formulae I am putting into the background colour field of the expression.

The second part of the expression, max(aggr... evaluates as expected. The first part obviously gives the total if check outside of the context of the graph's dimension [Fiscal Quarter and Year]. If I replace the second part of the expression with a number it works.

Any help gratefully received,

James

=if(Count ({$<[Day in Fiscal Quarter]={'-1'}>} DISTINCT [Order #])=max(aggr(Count ({$<[Day in Fiscal Quarter]={'-1'}>} DISTINCT [Order #]),[Fiscal Year and Quarter])),lightblue())

[jonathandienst]

James

I think you may need a TOTAL statement in the RHS of the comparison.Something like:

=if(Count ({$<[Day in Fiscal Quarter]={'-1'}>} DISTINCT [Order #]) =
max(aggr(Count ({$<[Day in Fiscal Quarter] = {'-1'}>} DISTINCT TOTAL [Order #]),[Fiscal Year and Quarter])),lightblue())

HTH

Jonathan

[#3]

Thanks Jonathan, unfortunately this doesn't appear to be the solution. (By the way, what does the abbreviation RHS stand for?)

The second part of the formula, the one that gives the max value, works when used on its own in a text box. As does the first part, although it gives the total value outside of the context that would be provided by the chart (The value for all quarters aggregated, rather than the value for any given quarter- the value which I am trying to evaluate against the maximum value.)

Hopefully I have been clear.

James

[jonathandienst]

RHS = right hand side

[jonathandienst]

James

Then I am not sure. I suggest that you post the QVW (or a sample of it) to illustrate the problem and I (or others) can look at it in more detail.

Jonathan

[#6]

Hi Jonathan,

Attached is an example QVD. The formula in the background colour is different to the example originally posted, but I am hoping the principle is the same. I am trying to achieve different bar colours for the max and min quarters.

Thanks again,

James

[matt_crowther · Accepted solution]

James,

Possible solution attached - use the rank() function to calculate the top and bottom values - may need some tweeking via the Help guide:

For the max it's simply if the rank of the sum of the Order Count =1 and for the lowest we simply rank the total orders minus the orders for that group and again if it's 1 we colour it - just look at the background expression and all will be clear!

Hope that helps,

Matt - Visual Analytics Ltd

[#8]

Thank you both very much for your help.