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: 
Not applicable

Dynamic Date Range using Max

I want to create a formula that will return the average of sales within 30 days of the latest date in the period I select within my app.  I have tried several approaches, but they aren't giving me the result I would expect.

1 Solution

Accepted Solutions
sunny_talwar

May be like this:

Avg(Aggr(Sum({<DateField = {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>}Sales), DateField))

View solution in original post

4 Replies
sunny_talwar

May be like this:

Avg(Aggr(Sum({<DateField = {"$(='>=' & Date(Max(Date) -30, 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>}Sales), DateField))

Not applicable
Author

Sorry - this actually looks a bit off.  Not 100% sure what's driving the outage, but the Sales amounts in the for the dates in question are pretty different in the visualization where I use that formula vs. a basic sum of the Sales for the same period in a basic table.  I'll dig in and let you know if I determine outage.

sunny_talwar

What are your chart dimensions?

Not applicable
Author

Using your suggestion, I believe I would be using:


Avg(Aggr(Sum({<[CloseDate]= {"$(='>=' & Date(Max(Date) -30, 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY'))"}>}[Bookings]), [CloseDate]))