Yes, I was able to obtain the required months by changing the expression formula.
I found one more method, where we can add a dimension directly to get the month number the formule used was " If(Aggr(sum(Sales) < Avg(TOTAL Sales),Month),Month)", but the problem over here is we have to select the "Suppress when value Is null"
But is there any other way where we can mention the condition and restrict the data. Like the filter options which we have in business objects.
Won't the Calculation Condition in General Tab or the column conditional in the presentation tab help? If no, then when should these options be used for?
firstly I work on v8.5 so my answers may not be the most up to date.
The calculation condition on the general tab governs the calculation of the chart as an object. This will be evalauated once for the whle chart and not record by record for a given dimension as you would want in your example. The calculation condition is useful when you have a large chart and would like to force your users into making some selections before running the chart. Or if running the particular chart has no sense unless certain conditions have been met.
The conditional column in the presentation tab must be post v8.5 but I would imagine that again it will hide or display the entire dimension and not certain elements of the dimension depending on the conditions set.
As for the filter object from BO I have no hands on practice of BO but do know that it is a sql generator. As such a filter object would play a part in generating the where clause of the sql query. The where clause wil restrict the rows sent back bythe query. Qv operates differently, the results in your cahrt can be affected by any selections made anywhere in the document so long as those selections operate on fields which can logically be joined back to your dimensions.
Your query however was to restrict dimensions elements depending on their value. To do this each row has to be queried anyway to check that its value was higher than the overall average. the we simply wanted to exclude those rows that failed this test. from a performance perspective the expression I gave you does this well enough as it tests the result row by row in the dimension and only shows the required answer set. you could maybe work it another way by adding a calculated dimension but I doubt there would be any performance benefits.
Hope thsi is useful and happy qliking!