4 Replies Latest reply on Nov 15, 2018 10:01 AM by Raheel Farooq

    First count the orders at id level and then median of the data at visible dimensions

    Raheel Farooq

      Hi All,


      I have a data structure where 1 id is associated with multiple orders. I am trying the find the median orders placed in a given time period (this is my dimension i.e. calendar date).

      for example if the data looks like below:


      orderdate      customerid     

      2018-01-01          001

      2018-01-11          001

      2018-01-20          002

      2018-01-22          003

      2018-02-02          002

      2018-02-10          002

      2018-02-15          001

      2018-02-16          001

      2018-02-17          001

      2018-02-17          003


      Then for the monthly view the results should be like:

      Calendar Date           Orders Median

      2018'Jan                      1                    (Reason of this is because 001 id placed 2 orders, 002 id placed 1 order and 003id also placed 1 order in 1st month Jan; for median sorting them would result in 1,1,2 so the median is 1 and that desired result)

      2018'Feb                      2                     (Reason of this is because 001 id placed 3 orders, 002 id placed 2 order and 003id placed 1 order in Feb; for median sorting them would result in 1,2,3 so the median is 2 and that desired result)


      The approach i am currently using is:

      -     I am trying to use Fixed lod to count the total orders placed by each customer

      -      then on the top of above fixed lod calculation i want to use window_median function to find the median


      But I am unable to get the right number and I am also not sure if this approach is correct or not?

      kindly suggest. Thanks


      I would appreciate if you please created the calculated fields too. Thanks