3 Replies Latest reply: Sep 17, 2012 8:19 AM by Henric Cronström RSS

    Mean and Std deviation for calculated fields

      Hi,

       

      I have a histogram (Bar chart) which displays the chart based on dynamically calculated fields from existing fields. The following are the formula I use to calcuate the Dimensions and expressions for my histogram.

       

      1. Dimension -> =Aggr(DISTINCT ROUND(NotionalUSD/1000000, .1), NotionalUSD)

      2. Expression  -> =Count(ROUND(NotionalUSD/1000000, .1))

       

      What I want to do is to calculate the Mean and standard deviation for this histogram. Can somebody please help me how I can do it for these calculated fields. I know we use Avg and Stddev functions but please give me an example on how to use it in my case.

       

      Secondly, I want to display the Mean and Standard Deviation values inside the chart. Where can I display them in the chart and how?

       

      Thirdly, My chart is a cyclic chart. In the sense, I have multiple dimensions and expressions. Whenever the user changes the dimension and expression the mean and SD must also change. How can we achieve this?

       

      Cheers

      Ranjith

       

      Thanks,

      Ranjith

        • Re: Mean and Std deviation for calculated fields
          Henric Cronström

          It seems as if you use the the Round() function to create classes of the number NotationalUSD, and then make a histogram of these classes. Correct?

           

          If so, I suggest that you define the classes already in the script by adding a field in your Load statement:

          ROUND(NotionalUSD/1000000, .1) as X

          Then you can use this as dimension and Count(X) as expression. Using a calculated dimension for something that you can define in the script is inefficient.

           

          Secondly, if you want to calculate the average within each class, you should use

          Avg(NotationalUSD) or StdDev(NotationalUSD)

          Calculating the average on the rounded number doesn't make sense.

           

          If you instead want to calculate the average for the entire chart, you can do this in the title or the label of the chart. Use the same formula with an equal sign in front:

          =Avg(NotationalUSD)

           

          HIC

            • Re: Mean and Std deviation for calculated fields

              Hi,

               

              Thanks. Just using Avg(NotionalUSD) and Stddev(NotionalUSD) helped.

              Now, next thing I want to do is display these values inside the chart (Something like Mean=xx.xxx and stddev=yy.yyy). Also, please note that my chart has cyclic dimensions and expressions. When I select NotionalUSD as the dimension I want the mean and stdev calculated and displayed for NotionalUSD. When I click on say another cyclic field "XYZ" i need to display the mean and stddev of "XYZ".

               

              How do I get this done?

               

              Ranjith

                • Re: Mean and Std deviation for calculated fields
                  Henric Cronström

                  Displaying these values is simple. Just choose Avg(NotationalUSD) as expression and mark "Values of Data Points". Or use the following as chart title/label: ='Mean=' & Avg(NotationalUSD) & ' and stddev=' & StdDev(NotationalUSD)

                   

                  Linking the dimension and expression so that the expression changes when you change dimension is worse. I do not see any simple way of doing this. I would suggest that you define both groups (cyclic dimension) and cyclic expressions.

                   

                  HIC