2 Replies Latest reply on Nov 24, 2016 8:50 AM by chandra shekhar banerjee

    Calculation of median from 4 calculated measures

    chandra shekhar banerjee

      Hi All,

       

      I have 4 calculated field which are count of orderids for 4 weeks

       

      Calculation for count of order ids are mentioned below:

      Calculation:1

      countd(if DATEPART('week',[Order Date])=30 THEN [Order ID] END)

      Calculation:2

      countd(if DATEPART('week',[Order Date])=29 THEN [Order ID] END)

      Calculation:3

      countd(if DATEPART('week',[Order Date])=28 THEN [Order ID] END)

      Calculation:4

      countd(if DATEPART('week',[Order Date])=27 THEN [Order ID] END)

       

      from this 4 aggregated values, I required to find out the mean. So if the output of above mentioned 4 calculations are 30,20,50,60 then I require median of those 4 numbers.

       

      I tried to create pivot in datasource level whcih is not happening or I might be doing wrong somewhere.

       

      Kindly help me to solve the issue.

       

      Thanks,

      Chandra Shekhar

        • 1. Re: Calculation of median from 4 calculated measures
          Chris Geatch

          Maybe someone will have a nice, short solution, but this gets the job done, just search and replace the value fields with your field names:

           

          ([Value 1] *

              (IF (([Value 1]>[Value 2] and [Value 1]>[Value 3] and [Value 1]<[Value 4]) OR

                   ([Value 1]>[Value 2] and [Value 1]<[Value 3] and [Value 1]>[Value 4]) OR

                   ([Value 1]<[Value 2] and [Value 1]>[Value 3] and [Value 1]>[Value 4])) then 1 else 0 END)

          +

          [Value 2] *

              (IF (([Value 2]>[Value 1] and [Value 2]>[Value 3] and [Value 2]<[Value 4]) OR

                   ([Value 2]>[Value 1] and [Value 2]<[Value 3] and [Value 2]>[Value 4]) OR

                   ([Value 2]<[Value 1] and [Value 2]>[Value 3] and [Value 2]>[Value 4])) then 1 else 0 END)

          +

          [Value 3] *

              (IF (([Value 3]>[Value 2] and [Value 3]>[Value 1] and [Value 3]<[Value 4]) OR

                   ([Value 3]>[Value 2] and [Value 3]<[Value 1] and [Value 3]>[Value 4]) OR

                   ([Value 3]<[Value 2] and [Value 3]>[Value 1] and [Value 3]>[Value 4])) then 1 else 0 END)

          +

          [Value 4] *

              (IF (([Value 4]>[Value 2] and [Value 4]>[Value 3] and [Value 4]<[Value 1]) OR

                   ([Value 4]>[Value 2] and [Value 4]<[Value 3] and [Value 4]>[Value 1]) OR

                   ([Value 4]<[Value 2] and [Value 4]>[Value 3] and [Value 4]>[Value 1])) then 1 else 0 END)

          +

          [Value 1] *

              (IF (([Value 1]<[Value 2] and [Value 1]<[Value 3] and [Value 1]>[Value 4]) OR

                   ([Value 1]<[Value 2] and [Value 1]>[Value 3] and [Value 1]<[Value 4]) OR

                   ([Value 1]>[Value 2] and [Value 1]<[Value 3] and [Value 1]<[Value 4])) then 1 else 0 END)

          +

          [Value 2] *

              (IF (([Value 2]<[Value 1] and [Value 2]<[Value 3] and [Value 2]>[Value 4]) OR

                   ([Value 2]<[Value 1] and [Value 2]>[Value 3] and [Value 2]<[Value 4]) OR

                   ([Value 2]>[Value 1] and [Value 2]<[Value 3] and [Value 2]<[Value 4])) then 1 else 0 END)

          +

          [Value 3] *

              (IF (([Value 3]<[Value 2] and [Value 3]<[Value 1] and [Value 3]>[Value 4]) OR

                   ([Value 3]<[Value 2] and [Value 3]>[Value 1] and [Value 3]<[Value 4]) OR

                   ([Value 3]>[Value 2] and [Value 3]<[Value 1] and [Value 3]<[Value 4])) then 1 else 0 END)

          +

          [Value 4] *

              (IF (([Value 4]<[Value 2] and [Value 4]<[Value 3] and [Value 4]>[Value 1]) OR

                   ([Value 4]<[Value 2] and [Value 4]>[Value 3] and [Value 4]<[Value 1]) OR

                   ([Value 4]>[Value 2] and [Value 4]<[Value 3] and [Value 4]<[Value 1])) then 1 else 0 END)

          )/2

          • 2. Re: Calculation of median from 4 calculated measures
            chandra shekhar banerjee

            Hi Chris,

             

            Thanks a lot for your answer.

             

            Actually meanwhile I have figure out a answer for the same.

             

            I share my work here for your reference.

            calculation5

            max(max(calculation1,calculation2),max(calculation3,calculation4))

             

            This will give the highest value from the 4 values.

             

            Same way,

            calculation6

            min(min(calculation1,calculation2),min(calculation3,calculation4))

             

            This will give the lowest value from the 4 values.

             

            Now,

             

            ((calculation1 + calculation2 + calculation3 + calculation4) - (calculation5+calculation6))/2

             

            this will give sum of middle two values divided by 2 which is nothing but the median.

             

            Thanks,

            Chandra Shekhar