Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Dynamic Date Range using Max

May be like this:

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

4 Replies

Re: Dynamic Date Range using Max

May be like this:

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

Not applicable

Re: Dynamic Date Range using Max

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.

Re: Dynamic Date Range using Max

What are your chart dimensions?

Not applicable

Re: Dynamic Date Range using Max

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

Community Browser