I need to have a chart that shows all the active requests at the last day of each week, month or year.
The number of active requests is accumulative, so to get the number of active requests at a certain week, I need to get the number of requests active at the end of the previous week, plus the requests opened in the week we want to see, less the requests closed in that given week.
That is a problem, because if one week is selected the active requests in the week before becomes zero. To get around this problem I made my expressions ignore the date selections for any date before the last day in the selected period using SA expression. But this doesn't work in a chart with date as a dimension because the SA expression is calculated only once per chart, and therefore if I have a requests that is active for some weeks, but is closed before the last week of the chart, that request will always appear as closed, even in the weeks that it was active.
I tried two approaches to solve this problem. One was to get the number of active requests per week in the sql select statement on load. However, since the requests intersect several dimensions, I would have to get the number of active requests for each combination of week and all the dimensions, and I couldn't make a query to do that.
The other approach was to get a flag for each request saying if at each date (one for each last day of week, month or year) the request was active. This approach didn't work either because the queries for doing this took an unaffordable time to run.
Anyone has any solution for getting the result I need?
I attached a simplified version of my data model to this post.
