10 Replies Latest reply on Mar 21, 2018 9:33 AM by Mohamed Thahir Gaffar Hussain

    Count a measure and divide it by all business days?

    David Nilsson

      Hi,

       

       

      I can't share the whole dataset so I have tried to simplify it.

       

      I have a set of data with orders. Each order has a date.

       

      The data is built like this:

       

       

       

       

       

      I need to calculate how many orders each merchant has per business day the last two weeks. The problem is that when I filter out Saturdays and Sundays and  calculate "COUNT(IF datediff('day',[Date],[enter start date])<=13 THEN [Order] END) / Countd(date)"  I will get the amount of orders divided by the dates the merchant had orders on. not the total amount of days.

       

      ("Enter start date" is a parameter)

       

       

      So in this case I will get:

       

      Merchant A:

      5 orders / 4 days = 1,25 orders per day

       

      Merchant B:

      1 Order/ 1 day = 1 orders per day

       

      ---------

       

      What I want to get:

       

      Merchant A:

      6 orders / 5 days = 1,2 orders per day

       

      Merchant B:

      1 Order/ 5 days = 0,2 orders per day

       

       

      (Maybe needless to say but of course I need this to be dynamical so I can't divide by "5")

       

       

       

      I have scratched my head, searched the web and these forums but I can't seem to find the answer. Would be so grateful if you could help me.

       

      I have attached a workbook if you need it.