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
Jan 8, 2018 1:23 PM
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
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
Jan 9, 2018 1:04 AM
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
Hi Chaitanya
Check this out!
Check Sheet 2 in the attachment.
Regards
Pavan