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?

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.

• ###### 1. Re: Count a measure and divide it by all business days?

Hi David,

Thanks,

Bharat

2 of 2 people found this helpful
• ###### 2. Re: Count a measure and divide it by all business days?

Hi, Kumar,

Thank you but I'm not sure you understand the problem. Don't think about the business days for a moment and read the rest of the problem ( I can filter out week-ends).

I want to divide by ALL the dates for each merchant.

The problem is that Merchant B for example only have orders on 1 day. However I want to divide the orders by all the days.

Maybe there is another solution that would work?

We have a merchant, let's call the merchant Merchant X. Merchant X has orders every (business) day. Is it possible to create a calculation that divides the orders for each merchant by the count of merchant Xs order dates?

Example:

Orders for Merchant A

/ Countd(Dates for Merchant B)

?

• ###### 3. Re: Count a measure and divide it by all business days?

Hi David,

You can create two calculated fields one for weekend and the other for business days(excluding weekends) and then get the total number of records i.e all dates when you sum up both calculations.Please check the links i shared in my earlier reply it should help you out.

Thanks,

Bharat

1 of 1 people found this helpful
• ###### 4. Re: Count a measure and divide it by all business days?

Hi, David

In your sample data, is below screenshot is the result you are after?

ZZ

2 of 2 people found this helpful
• ###### 5. Re: Count a measure and divide it by all business days?

Hi David!

{FIXED [Merchant]:COUNTD( IF IFNULL([Order],0)!=0

AND DATEDIFF('day',([Date]), DATETRUNC('day', [enter start date]))<=13

THEN [Date]

END)}

2) Count Orders per Merchant:

{FIXED [Merchant]:SUM(

IF DATEDIFF('day',[Date], DATETRUNC('day', [enter start date]))<=13

THEN [Order]

END)}

3) Num of orders per BD:

[Count Orders per Merchant]/[Count Business Days]

Trust this helps.
D

2 of 2 people found this helpful
• ###### 6. Re: Count a measure and divide it by all business days?

Thank you all for your answers. Really appreciate it. However none seem to work all the way.

I have found that {EXCLUDE Merchant: etc...} and just a normal filter  that excludes Saturdays and Sundays almost brings me to where I want. I just need a formula after that that works with the parameter "Enter Start Date".

This almost works:

{EXCLUDE  [Merchant]:COUNTD( IF DATEDIFF('day',[Date], [enter start date])<=13  THEN [Date] ELSE NULL  END )}

The problem is that the parameter "Enter start Date" has to be the same as the latest DATE to work with this set up. If I put the parameter "Enter Start Date" earlier I will get a higher number.

How can I choose all dates (not filter away any except the Saturdays & Sundays) and get my parameter to work by putting any date there there so it will return the last 14 days?

2 of 2 people found this helpful
• ###### 7. Re: Count a measure and divide it by all business days?

Hi David,

Please explain what you want more clearly because both me and Zhouyi Zhang satisfied your original request.

BTW it would be nice to mark our responses as helpful in order to help other users in solving their issues. So Your Question Didn't Get Answered...

D

1 of 1 people found this helpful
• ###### 8. Re: Count a measure and divide it by all business days?

Hi David M,

Sorry I will attach a new workbook and of course I will put your answers as helpful.

In this workbook I have used the last 5 days instead of 2 weeks to simplify it even more.

At first everything seems fine but when you change the "Enter Start Date" parameter you will see that the "Last 5 Days" will be wrong. Put for example 2017-10-25 and you will get 5 days (when you actually should get 3).

This can be fixed partially if I change the date interval to the same date. So if I put 2017-10-25 as the last date as well as "Enter Start Date" To 2017-10-25 it will be correct.

However I want to remove the date filter and only use the parameter.

How do I get that to work?

The solution is to fix this formula I guess:

{EXCLUDE  [Merchant]:COUNTD( IF DATEDIFF('day',[Date], [enter start date])<=4  THEN [Date] ELSE NULL  END )}

• ###### 9. Re: Count a measure and divide it by all business days?

David,

I am quite confused why my solutions doesn't suits. You want to get the exact number of business days when merchant had orders that satisfy date diff condition, am i right?

So, if that's true:

{FIXED [Merchant]:COUNTD( IF IFNULL([Order],0)!=0

AND DATEDIFF('day',([Date]), DATETRUNC('day', [enter start date]))<=13

THEN [Date]

END)}

We fix each merchant and count days where order is not null (that's how we exclude weekends) + count only dates that satisfy our date diff condition.

The same logic applies to another formula where we sum orders:

{FIXED [Merchant]:SUM(

IF DATEDIFF('day',[Date], DATETRUNC('day', [enter start date]))<=13

THEN [Order]

END)}

Trust this helps.

D

• ###### 10. Re: Count a measure and divide it by all business days?

Hi David ,

I have created few formulas for the two weeks of data in your workbook.  Please check if this would work for you.  You may have to change the formulas a little if you have more than 2W data or some other scenarios.  Check Sheet 3 in attached workbook.  No need to filter sat/sun or the slider.  This works based on the parameter date

Thanks,

Mohamed