topic Re: Get the last 5 weeks that a customer ordered in QlikView

Get the last 5 weeks that a customer ordered

djbloiss — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Get the last 5 weeks that a customer ordered

Anil_Babu_Samineni — Mon, 08 Jan 2018 14:08:41 GMT

Perhaps this?

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

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 14:11:48 GMT

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

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 14:14:41 GMT

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?

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 14:16:07 GMT

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.

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 14:25:32 GMT

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.

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 14:33:53 GMT

I think case, you should be able to do this

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

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 14:54:13 GMT

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

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 14:58:51 GMT

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?

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 14:59:53 GMT

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

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 15:00:51 GMT

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

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 16:53:14 GMT

Hi Sunny,

I just some sample data.

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 17:26:06 GMT

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

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 18:15:58 GMT

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

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 19:17:11 GMT

Check here

What does the TOTAL qualifier do?

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 19:28:50 GMT

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.

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 19:30:55 GMT

Are you making selections which might be throwing this off?

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 19:50:53 GMT

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.

Re: Get the last 5 weeks that a customer ordered

sunny_talwar — Mon, 08 Jan 2018 19:54:58 GMT

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

Re: Get the last 5 weeks that a customer ordered

djbloiss — Mon, 08 Jan 2018 20:15:48 GMT

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.