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

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

Regards,

RF

• ###### 1. Re: First count the orders at id level and then median of the data at visible dimensions

Create an LOD that counts the number of orders for each customer for each month:

{FIXED [Customerid], YEAR([Orderdate]), MONTH([Orderdate]): COUNTD([Orderdate])}

Then you can take the median of that on your view.

• ###### 2. Re: First count the orders at id level and then median of the data at visible dimensions

somehow it is not allowing me to calculate median on LOD calc field. and making it RED ; however giving me the option to use window_median function (since I am using LIVE connection not the extract and this is one of the limitation i have to use live connection in any case)

and what is the reason you calculated on id, year,month

{FIXED [Customerid], YEAR([Orderdate]), MONTH([Orderdate]): COUNTD([Orderdate])}

and not only on id

{FIXED [Customerid]: COUNTD([Orderdate])}

• ###### 3. Re: First count the orders at id level and then median of the data at visible dimensions

My LOD calculates the number of orders for each customer for each year/month. The LOD on just ID would could the number of orders for that customer for any year/month.

• ###### 4. Re: First count the orders at id level and then median of the data at visible dimensions

Thanks @Ken.

But I am still struggling with window_median thing...could you please suggest any idea to use window median function instead of median function since i am using live connection and median function is not available in live connection. Thanks much