Get the last 5 weeks that a customer ordered

[djbloiss]

I am new to QlikView and am working on a project that is quite intensive.  We need to look at each customer in a straight table and do the following:

1) Determine when was the last 5 weeks that each customer purchased

2) use that within set analysis

this is currently what I have:

sum({WEEK_COUNT =,[Invoice Number]={">=$(=Max([Invoice Number])-5)<=$(=Max([Invoice Number]))"} >} [Ext sales-net])

but there are 2 problems with that.  First, it only looks at the last 5 customer orders; regardless of the weeks it was in.  Second, every customer comes out wtih zero right now when it is unfiltered but when you click on a customer it then shows a value.  Something is telling me this must be done within the script editor, not within the table itself.  Can someone help at least point me in the right direction?

[Anil_Babu_Samineni]

Perhaps this?

sum({WEEK_COUNT =,[Invoice Number]={"=Customer>=$(=Max([Invoice Number])-5) and Customer<=$(=Max([Invoice Number]))"} >} [Ext sales-net])

[sunny_talwar]

I think it would be best to not use set analysis in this case, but use Aggr() with if statement.... Set analysis is evaluated once per chart and will not give you max invoice number per customer....

[sunny_talwar]

May be this

Sum(Aggr(If([Invoice Number] >= (Max(TOTAL <Customer> [Invoice Number]) - 5) and [Invoice Number] <= Max(TOTAL <Customer> [Invoice Number), Sum([Ext sales-net])), Customer, [Invoice Number]))

Don't really understand this issue

First, it only looks at the last 5 customer orders; regardless of the weeks it was in.

What exactly do you mean here?

[djbloiss]

Anil, I just tried that to see if it will get me closer to what I need but now it always comes out as zero.  The Field I would need to use for Customer is [Customer Code] but it stilll comes as zero.  Also, looking at that syntax it seems to still only look at the last 5 orders regardless of what weeks they are in.  if there are 10 orders in last week then it would only look at last week but what I want is to look at that week and the 4 weeks prior that they had orders.

[djbloiss]

Take this hypothetical data for a customer, assuming the week we are in is 58

Week#	Amount or Orders
57	10
54	5
50	1
49	1
48	12
45	5

With that syntax it will look at last week (Week#57) and choose the last 5 orders within there and ignore the other weeks.  However, what we want is to look at week 57, 54, 50, 49, and 48 (and it would not look at 45) because those are the last 5 weeks that the customer purchased, regardless of how many orders were within 1 week.

I made modifications to your syntax. Added set analysis that we do need and fixed the name of the field, it isn't Customer, it is [Customer Num].  However, I am always getting zeros now.

[sunny_talwar]

I think case, you should be able to do this

>= Max(Week#, 5)<=Max(Week#)

[djbloiss]

Sunny but how will I match that up with the weeks that each customer purchased?

[sunny_talwar]

I think it would be better if you can share a more comprehensive sample data and explain what is the expected output needed from it... would you be able to do that?

[djbloiss]

I am new to the forum, what is the best way to to do that? Create a fake qvw file with data and structure?