Order cycle

[#1]

hello, all:

To see ordering trends, I'd like to compute the minimum, maxiumum and average date differences between two adjacent orders.And then the cycles can be shown in a line chart.

From above, for company 3, min. order cycle=1 day(2-23 minus 2-22), max.cycle=4 days(2-22 mimus 2-18), and avg cycle≈2.33 days.

I've set the dimension as "customer". Anyone can help to suggest an expressions?

[Gysbert_Wassenaar · Accepted solution]

You could calculate this in the script or with expressions in a chart. See attached qvw

edit: try this expression for the minimum interval

min(total <Company> aggr(OrderDate-above(OrderDate),Company,OrderDate))

for maximimum and average use the max and avg functions instead of min. You'll have to make sure your dates are ordered ascending or you're going to get funny figures.

[#3]

Hi, Gysbert:

Thank you. But I'm trying the personal edition, unable to open ur qvw. Could u pls write out the script or expression here? 

[#4]

It works. Thanks a ton.

If the dimension is a expression rather than field name, it seems the expression can not get the right results. Did you have a test on it?

e.g. In my case, if company 1 and company 2 are associated. I may need to get the minimum order cycle from the group companies. Then I supposed:  min(total <if(Customer="Company1" or Customer="Company2", "Group1", If(Customer="Company3","Group2","Group3"))> aggr(OrderDate-above(OrderDate),if(Customer="Company1" or Customer="Company2", "Group1", If(Customer="Company3","Group2","Group3")),OrderDate))