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: 
mruehl
Partner - Specialist
Partner - Specialist

Average Sum of the last 5 Orders per Customer

I want to analyze customers, who have slightly decreasing order amounts.

So I want to get the average of the last 5 orders dynamically in the chart. Maybe I want to change from last 5 to last 10 or want to set the analyzed timeline.

I tried differnt options with set analysis, but nothing worked.

In the attachment I have some sample data.

Thanks in advance for your help!

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Try this

 

Sum(Aggr(
  If(date >= Max(TOTAL <customer> date, 5) and date <= Max(TOTAL <customer> date), Sum(amount))
, date, customer))

 

View solution in original post

3 Replies
sunny_talwar

May be something like this

Avg(Aggr(If(date >= Max(TOTAL <customer> date, 5) and date <= Max(TOTAL <customer> date), Num(amount)), date, customer))

mruehl
Partner - Specialist
Partner - Specialist
Author

@sunny_talwar 

Sorry for my late response.

It works almost perfect.

But if a customer places more than one order per date, the formula doesn't work.

 

sunny_talwar

Try this

 

Sum(Aggr(
  If(date >= Max(TOTAL <customer> date, 5) and date <= Max(TOTAL <customer> date), Sum(amount))
, date, customer))