topic Grouping customers by date last ordered in QlikView

Grouping customers by date last ordered

Wed, 01 Oct 2014 13:26:22 GMT

I need to group customers in to 'buckets' depending on the
last time they purchased.

This needs calculate from the max order date - when 0 selections are made,
or filter by the max date when an item or geographical location is selected.

Originally I had tried to write this into the script, but I can only get it
to work for the max order date over all.

Would this be better calculated with in the chart?

I need to display results in a bar chart.

see example below;

Re: Grouping customers by date last ordered

anbu1984 — Wed, 01 Oct 2014 13:32:58 GMT

Use class function to create buckets in chart

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

Re: Grouping customers by date last ordered

MarcoWedel — Wed, 01 Oct 2014 21:23:12 GMT

Hi,

one solution could be (at front end):

hope this helps

regards

Marco

Re: Grouping customers by date last ordered

Thu, 02 Oct 2014 08:24:23 GMT

Hi Marco

Thank you for this its working really well.

I just have one more question - i'm stuggling to put this expression into an if statement so that customers with 0 value, or 0 orders are not included. - can you advise?

Thanks again

Heather

Re: Grouping customers by date last ordered

anbu1984 — Thu, 02 Oct 2014 12:30:16 GMT

=If( Aggr(Div(Today()-Max([order date]),7), customer) > 0,

Dual (...)

)

Select "Suppress when value is null"

Re: Grouping customers by date last ordered

Thu, 02 Oct 2014 12:48:12 GMT

Thank you this have solved my issue!

Re: Grouping customers by date last ordered

Thu, 23 Oct 2014 11:16:29 GMT

i've reverted back to the orignal expression

Dual(Replace(Class(Aggr(Div(Today()-Max(Date),7),Customerr),4),'<= x <','-'),

Class(Aggr(Div(Today()-Max(Date),7),Customer),4))

so that I can include customers that have ordered less than a week ago, however this is causeing me an issue with the bucket labels.

Because '0' now takes up positiion 1 as it were, bucket 0-4 only contains the count of customers that ordred in 0-3 weeks. customers that ordered 4 weeks ago now sit in the 4-8 bucket, which containts customers that ordered between 4 and 7 weeks ago and customer that ordered 8 weeks ago sit in bucket 8-12....and so on.

How can I re-lable the buckets to reflect what's actually in them?