I’m trying to build a dashboard with a chart view and I’d like to start the BAQ with a dataset of specified dates. That is, the top level data should be “today” + 30 days, resulting in a set of dates:
2018-11-08
2018-11-09
2018-11-10
…
2018-12-08
From this top level dataset, I’ll build subqueries to extract specific info for each date.
Is there any way for me to auto generate this dataset in a BAQ?
In the BAQ, there are options for criteria. One of them is today + (day/moth/year etc). You can set the dynamic filters there. Do you have a BAQ started yet?
As far as getting those dates, I usually grab a table that has the dates I will need and set the criteria that way,. then only show the date and group by it. That gets me a list of dates. It only gets ones that already exist in the table, but most of the time, I’ve found that is acceptable.
The other option is to make a CTE query that loops through to build the data set. That’s a pain.
I have the other pieces of the BAQ built out, just need this top level to start with a list of dates. I don’t believe I can base the date set off of another table because the range will typically be 30+ days in the future. If I did try this, which table would be a good source with lots of future dates? Joboper?
What are you joining the dates to? Why not use that table? (I’m probably not understanding the end use case here, so that may not be a valid suggestion)
If the “+30” is constant, just add a calculated field to the with the formula being the date field you use to filter (say it’s “Field_you_use_to_filter”), with 30 added to it.
Then in the dashboard, keep that newly added field (we’ll call it “Calculated_FieldAdded”) hidden.
Set 2 filters on the Grid to be: