Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
djbloiss
New 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

Re: Get the last 5 weeks that a customer ordered

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

20 Replies

Re: Get the last 5 weeks that a customer ordered

Perhaps this?

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

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)

Re: Get the last 5 weeks that a customer ordered

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

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

Re: Get the last 5 weeks that a customer ordered

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

Re: Get the last 5 weeks that a customer ordered

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.

Re: Get the last 5 weeks that a customer ordered

I think case, you should be able to do this

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

djbloiss
New Contributor III

Re: Get the last 5 weeks that a customer ordered

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

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

Re: Get the last 5 weeks that a customer ordered

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