Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
djbloiss
Contributor III
Contributor III

Get the last 5 weeks that a customer ordered

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?

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

20 Replies
Anil_Babu_Samineni

Perhaps this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Week#
Amount or Orders
5710
545
501
491
4812
455

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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