I would like to change the calculation based on what data range is being selected. e.g. count of login/date range selected. Is it possible to pass 'date range' to beast mode?
MIN would return the first date in that range with values and Max would return the last date in the range with values. If you have data on every date, it would work like you're hoping. If you don't have data until Jan 4th, in your example, MIN(`date`) would return Jan 4th, even though they chose Jan 1st as the beginning of the date range.
Does that make sense?
@zcameron correct me if I'm wrong, but aggregate functions like MIN() will also be influenced by the chart type chosen and if there are series groupings within that chart. Each stack segment in a stacked bar chart, for example, could have a unique MIN() value or MAX() date value (transactions by division maybe). A MAX() in a pie chart might behave differently than it would in a line chart or a gauge.
So @magicdust, be aware of the underlying groupings that MIN() and MAX() might apply to and how widespread your data is across the dimensions included in the dataset.
Can anybody help @magicdust with their question?Thank you!
The date range isn't available as a variable or function in the beast mode. In some cases, though, you can use a datediff to figure out how many days the range represents.
Here's an example:
SUM(`login_count`) / DATEDIFF(MAX(`date`), MIN(`date`))
Not sure if this will fit your use case, but it's something you could look at.
Let me know if that helps.
@magicdust, did zcameron's response help answer your question?
Thanks for the reply, however I want to calculate based on what user has selected in the date, not on min and max from dataset.
Just as a note, the MIN and MAX functions will return the MIN and MAX of the filtered data, not the entire dataset. That means that if the user selects This Month, the MIN would return the first day of this month with values and the MAX would return the last day of this month with values, even if the dataset covers much more time than just this month.
I can understand what you're after, though, and agree that it would be great to be able to access the selected date range in a beast mode. If it isn't there already, I think it would be a great addition to the Ideas Exchange.
Instead of selecting 'this month', if user selects date range e.g. 1- Jan 2016 till 20-Jan 2016, what would happen?
Makes sense! Thanks
This is really good point @AS Thanks for sharing it