4 Replies Latest reply on Oct 18, 2018 7:55 AM by Ombir Rathee

Count active orders in end of every month

Hi,

I want to make a cross table to find out how many active contract we have on the last day of a month and compare those between years.

In our data I have start_date and end date.   So the order is valid if start_date < date X and end_date>date_X.

I can make a calculated item for every date I want to investigate but I guess there is more effective way to do this?

31.128.231.330.4...
31.12
20173 2003 1002 90012001 350
20182 9004 2001 9501 5001 600

Our data:

Order_idProduct_idstart_Dateend_date
158962212.3.201619.5.2018
216587214.7.201728.9.2017
138779316.7.201820.9.2020
154687125.10.201714.2.2018
216533321.6.201714.8.2018
235411123.9.201624.9.2018
• 1. Re: Count active orders in end of every month

Tanja,

I have created another sheet with the list of month end values like 12 values for each year and then joined with the data on 1=1 (using calculation join) and then derived the below calculation field as :

Attaching the workbook for your reference.

• 2. Re: Count active orders in end of every month

What is the date X in your example. Is it end of month of start date or end date ?

• 3. Re: Count active orders in end of every month

My date x is end of month

• 4. Re: Count active orders in end of every month

Still not clear. End of month of which month. If you take the first row in your example then date x will be eom for start date or end date ? Can you post a sample workbook with your expected output ?