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?

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If([Week Count] >= (Max(TOTAL <[Customer Code]> [Week Count]) - 5) and [Week Count] <= Max(TOTAL <[Customer Code]> [Week Count]), Sum([Ext Sales - Net])), [Customer Code], [Week Count]))