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]

You can either do that or just share raw data in an Excel file

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[djbloiss]

Hi Sunny,

I just some sample data.

[sunny_talwar]

This?

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

[djbloiss]

Sunny can you explain what the Total keyword is doing here?

[sunny_talwar]

Check here

What does the TOTAL qualifier do?

[djbloiss]

Sunny,

Thank you that helped me understand what Total does but not how to fix my issue.  The script you gave me works on the small workbook that I faked and sent to you.  However, it does not work on the real workbook within the organization.  It is still coming out to be all zeros in the real workbook.   I thought I supplied you with all the relevant fields and where they are located.  What might I be missing that I would need to send you to prevent this? I thought all the fields that were used in the expression would be sufficient but it seems not. 

[sunny_talwar]

Are you making selections which might be throwing this off?

[djbloiss]

I figured it out.  Even though it was saying the Expression was okay; there was an issue with one of the fields case sensitivity.

Thank you for the help.  This is half way there.  This fixed the issue of the difference between the full list and when I click on a customer.  However, it still is only looking at the last 5 invoices, not the last 5 weeks of each customers invoices?  Each customer I need to look at what were the last 5 weeks they purchased and then get all the invoices within those weeks. 

[sunny_talwar]

I am sort of confused.... you want to look at last 5 transactions or last 5 weeks of transactions? or both (last 5 transaction within the last 5 weeks)?

[djbloiss]

I want to look at the last 5 weeks for each customer.  So not the previous 5 weeks from the current week.  The previous 5 weeks each customer purchased.