I have a BAQ that provides me with amongst other things, a Max of a Date field.
However, it shows all results from go-live 5 months ago and the users department want to filter only the last 7 or 14 days worth any time they look at the BAQ.
The Date itself comes from a calculated field (LastDate) in a subquery and I have tried and failed to create something that looks at > LastDate - 14 probably by bad syntax or something else I am not sure.
Can anyone point me in the right direction please?
Cheers.
Thanks for the suggestions but I am not getting past syntax errors.
The first calculated field chooses the LastDate from a set of Delivery Dates with the code āmax(SerialNo2.CsgDeliveryDate_c)ā
I have tried to add a second Calculated field called Last2Weeks with the suggestions above but I get an error validating the syntax each time as follows:
The calculated field āLastDateā is used in another calculated field āLast2Weeksā and its definition will be substituted there. Make sure it is expected behavior.,
Christian, at which level should the subquery criteria be at?
I have the original calculated field in a subquery inside a subquery.
Am getting the error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
EDIT. I ticked the Having box and the report runs. The LastDate field is empty for some reason.
It depends on your query so itās hard to say without seeing it. If I use a subquery criteria itās usually on top-level, but thatās just because of the way I build them. Iām also not 100% sure of the rules on putting them in subs.
Do you only care about parts where the SerialNo2.CsgDeliveryDate_c is within the past 14 days?
Do you not want to find a max date if itās more than 14 days ago?
Right now without the subQ criteria it was giving a Max date for all jobs delivered, but the Sales guys want to limit to a view of the last 14 days (which they could easily have done with an Excel export!).
They initially asked for a selection query so they could decide on the fly but at this moment that is beyond me in a BAQ so I thought I would at least give them a 14 days option and take it from there.
Try this instead of the subquery criteria.
In your lowest query, where the max date is calculated. Put a table criteria on the SerialNo2 table.
CSGDeliveryDate_c >= DATEADD(day, -14, Constants.Today)
Sounds like youāre trying to first calc the date then filter that. If so, your criteria should be at least one query āhigherā. Meaning outside the sub that does the calc. Try it in a query that sources from the one that calcs.
I think it is complicated. What we do is make Trailers and we build/invoice them in a batch.
The Sales guys want to know when the āLast of Batchā has been invoiced which is tricky in itself because it is not always the linear last trailer in a batch that is the last to be completed, we often build the batch out of sequence for many different reasons. This is what the Max calculated field was meant to be identifying.
Personally, I think I would prefer it if they had all the data then exported to Excel and did their own filters there, so nothing is missed by an incorrectly filtered BAQ.
What I would like to do is filter out the lines with no DeliveryDate but this is just not working.
I am filtering saying CSGDeliveryDate_c = Not IsNull and/or Calculated_LastDate = Not IsNull but still I get the entire list.
Iām going to park this one and return to it later as it is burning my time.
I appreciate the help, if nothing else it has been very useful in learning me a bit more detail on Subqueries and the effects of different levels.
A different idea would be to filter on your dates on the dashboard instead of in the BAQ. You can either put the date in the tracker as a filter, or assign a static filter on the grid. Then you donāt have to worry so much about the interaction between your filter and the calculation.
When you filter the date on the tracker is just shows you everything up to that date, I am not sure how to make it show everything from a selected date.
