4 Replies Latest reply: Mar 25, 2010 5:32 AM by John De Loach RSS

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

       

      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



        • Limit the showing lines in a chart by a calculation in the chart
          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.

            • AW:Re: Limit the showing lines in a chart by a calculation in the chart

              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



                • AW:Re: Limit the showing lines in a chart by a calculation in the chart
                  Michael Anthony

                  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