The formula you have is okay, I usually write it as follows:
Year(Date) * 10^4
+ Month(Date) * 10^2
+ Day(Date)
The above gives the same result, but takes less brain power because you don’t have to count the zeros 
Just taking a step back…
You are kind of on the right track, but not quite there.
- Firstly, you need two columns to store the user supplied start and end dates. These columns could be anywhere, as long as you can target them from the current screen. I usually have them either in the User Profile row, or in a single row helper table. If you put them anywhere other than the user profile row, then they must be user specific.
- Next you need two math columns to convert each of those to numbers in YYYYMMDD format, using the above formula.
- In your data tables, you need the same math columns to convert the date on each row to the same format
- Now add two query columns to the same table where you are capturing the start/end dates. Each column should target one of the data tables, and use the following filters:
– Start Date Math is equal to or less than This row->Start Date Math
– End Date Math is equal to or greater than This row->End Date Math - Now you should be able to do your rollups through the two query columns.