5 Replies Latest reply on Jan 9, 2018 4:44 PM by Chaitanya Somanchi

# Average number of days calculation

Hi All,

I have a view something like the one shown below. The calculation i used for "datediff" is DATEDIFF('day', [Order Date],[Ship Date]).

Please help me in writing a calculated field to determine the average number of days i.e : Sum(Min(datediff))/Countd(Order ID). If you look at the highlighted portion in the picture above it will be 6650/1687.

Attached the sample workbook i created using v10.4

Thanks

Chaitanya

• ###### 1. Re: Average number of days calculation

Hi Chaitanya,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread

1. M1. Total Amount of Days: window_sum([datediff])

2. M2. Count OrderID: WINDOW_COUNT(countd([Order ID]))

3.M3. Average: [M1. Total Amount of Days]/[M2. Count OrderID]

Regards,

Norbert

• ###### 2. Re: Average number of days calculation

Thanks Norbert. Solution works if i want to show it as a table. However i was looking to plot it on a bar graph to see the average across each quarter. When i was trying that it doesn't work. Can you also help me on this ?

Attached the workbook by filtering to 2017 Q4. The average shows 3.97 . However in the next sheet i was trying out the bar diagram it shows 0.62

• ###### 3. Re: Average number of days calculation

HI Chaitanya,

Find my updated approach below as reference and stored in attached workbook version 10.4 located in the original thread

1. D1. Order Date (Quarters)

2. M1. Datediff: {fixed [Order ID]:min(DATEDIFF('day', [Order Date],[Ship Date]))}

3. M2. Count OrderID: {fixed [D1. Order Date (Quarters)]:(countd([Order ID]))}

4. M3. Average: [M1. Datediff]/[M2. Count OrderID]

5. Drag the required objects to the indicated locations.

Regards,

Norbert

• ###### 4. Re: Average number of days calculation

Hi Chaitanya

Check this out!

Check Sheet 2 in the attachment.

Regards

Pavan

1 of 1 people found this helpful
• ###### 5. Re: Average number of days calculation

Thanks Pavan. This worked out