I need to count the number of active requests at a certain date, including requests that are outside of the current selection.
So far I have done the following: I have a variable that gets the current date (varCurrentDate), and I added this to the expression {<YEAR=,MONTH=,WEEK=,DATE={"<=$(varCurrentDate)"}>}. This succeeds in getting all the active requests at a certain date, regardless of the dates outside of the selection.
This expression works fine in most cases, but in charts that use a date hierachy as a dimension (YEAR->MONTH->WEEK) the result is not what I want. Since the variable holds the maximum date of the chart, it only counts the requests that are active at that date (maximum chart date), instead of counting the requests that are active at each date in X-axis value (whether it's year, month or week).
Is there any way to get the date at each value in x-axis and put it in set analysis?
Unfortunately, Set Analysis can't be sensitive to the Dimension values, because SA Expression is calculated once per chart and not once for every cell...
You need to clarify what kind of condition do you need for each one of your Date dimensions (Month, Week, etc...) - is it the last date in the period, or simply all dates, and formulate your expression accordingly. Sometimes, preparing a flag in your data structure could help.
For example, if you need to aggregate data for the last date in each Month, you could prepare a flag "LastDayInMonth" = 1 for all the dates that qualify. Then, your condition could be:
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 to approaches for solving 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.
