7 Replies Latest reply: Aug 1, 2012 3:57 AM by biel.ll.s RSS

    Indicator graphic

      Hi, I've created the following table with a red traffic light if the profit is negative:

      BusinessProfitTraffic light gauge
      Restaurant10,000green
      Bar-3,000red
      Hotel50,000green
      Pub-7,000red
      Supermarket100,000green

      Min = -7,000red (since minimum is -7,000, which is negative)

       

      As you can see, on the last row I have the minimum profit and a red traffic light gauge if at least one of the businesses have negative profit.

      I cloned this table and I've been trying to create an indicator graphic (with a traffic light) that is red if minimum of those busenesses has negative profit (which means at least one business has a negative profit).

       

      I can't use min(Profit) since profit isn't a dimension, but an expression that can only be calculated if the table has the dimension business.

        • Re: Indicator graphic
          Martin Pohl

          See attached file

           

          Regards

          • Re: Indicator graphic

            Jonathan and Martin, thank you for your answers, but I'm affraid my Profit expression is a little complicated and doesn't work with your suggestions.

             

            The expression is something like this:

            =if(Credit_Limit>0 ,sum(Pendent)-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth) ),Pendent,0))-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-1 ),Pendent,0))-sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-2 ),Pendent,0))-Credit_Limit, 0)

              • Re: Indicator graphic
                Jonathan Dienst

                Hi

                 

                Then the following should work:

                 

                Min(Aggr(

                If(Credit_Limit>0,

                          sum(Pendent)

                          -sum(if((RegisteredYear='2012'and RegisteredMonth=$(Vmonth) ), Pendent,0))

                                    -sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-1 ),Pendent,0))

                                    -sum(if((RegisteredYear='2012' and RegisteredMonth=$(Vmonth)-2 ),Pendent,0))-Credit_Limit, 0),

                Business))

                 

                If there are other dimensions, then replace "Business" with the list of dimensions:

                 

                Min(Aggr(........, dim1, dim2,  dim3))

                 

                Regards

                Jonathan