Limit the showing lines in a chart by a calculation in the chart

[#1]

Hi all experts,

I work for days on an issue and could not manage it.

I have a chart where I calculate a column with a criterion. Now I want to show only the lines with the criteria "A". I thought I could add a column with an if-formula [if "column criteria" = 'A' then 1 else 0]. Now I go to the tab presentation and limit the maximum lines with the formula [sum("column if") ] in the formula box "Max Number", but It doesn't work.

Does somebody have a solution how to limit the showing lines in a chart by a calculation in the chart? It has to by dynamic because the number of showing lines is changing.

Thanks for thinking about.

John

[michael_anthony]

John, Can you clarify what your trying to achieve.

Sounds like you trying to filter out all values from your chart where the criteria is not 'A'. I don't think Max Number is the way to do that. You could instead put your If statement into each expression, eg. Sum(If(Criteria = 'A',Value,Null())). The criteria will prevent any values being calculated and therefore no rows returned where A is not true.

The number of rows that do come through will change depending on how many meet 'A'.

there are other similar ways - eg. Set Analysis, using Calculated Dimension that returns Null where not 'A' then excluding Nulls, but they are variations on same theme.

[#3]

Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4 Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4

He Michael,

thanks for your answer! Sorry I had no chance to be online the last days.

My issue is that I'm too stupid to manage.

I got a chart with the Dimension Name and some columns with

calculations (eg percent ...) and the last one with the criteria 'A'.

The calculations in the columns are based on the calculations from the column before.

(eg. Volume | Percent of total Volume | aggregation of percent |

A-rating till the aggregation of percent is 50%).

And now I want to show only the names with the A-rating.

Can you help me please.

Thank you

Regards

[michael_anthony · Accepted solution]

Hi John,

I'm assuming this 50% cutoff is dynamic as selections change and can't be precalculated in script

If so its a tougher problem. I don't know of a standard solution. The best I could do was to use the above function in an expression which can test when the aggregation of percent hits 50% and after then return's null. But that leaves the rows in, even though value is blank.

I don't know how to apply

A workaround may be to use a macro which can determine the products (or the cut off point of volume). An example method is:

- Set up a linked field called Product_Temp which is copy of Product but is sorted (in doc properties) in descending order by Sum(Volume).

- Create a variable called vTotalVolume which is simply Sum(Volme).

- Use a macro which gets the total volume and goes through the Product_Temp field selecting them in order (of volume size) until reaches vTotalVolume * 50%

This would have to be re-applied every time selections changed.

Perhaps someone else knows a simpler way.

Sub

FilterSum

set

v = ActiveDocument.Variables("vTotalVolume"

)

String

* 1

msgbox

(total)

msgbox

(cutoff)

Set

f = ActiveDocument.Fields("Product_Temp"

)

Set

fV = f.GetPossibleValues

for

i = 0 to fV.Count

set

v = ActiveDocument.Variables("vTotalVolume"

)

String

* 1

If

cumtotal > cutoff

then

exit

for

end

if

Next

End

Sub

[#5]

Hi Michael,

thanks for thinking about!

I will try.

John