topic Re: Order cycle in QlikView

Order cycle

Sat, 04 May 2013 03:07:30 GMT

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?

Re: Order cycle

Gysbert_Wassenaar — Sat, 04 May 2013 07:47:01 GMT

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.

Re: Order cycle

Sat, 04 May 2013 08:15:31 GMT

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?

Re: Order cycle

Sun, 05 May 2013 02:42:45 GMT

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))