We have the revenues and expenses data month wise for each year.
We need to calculate the YTD for the selected month for revenues, expenses and PBT against several dimesions.
Besides that we need to show the values for the same month in previous year, previous month and moving averages.
The problem is owing to the associative nature in the model, on selection of filters (year, month), the sum (value) only reflects on the available data as the rest gets filtered out.
Please suggest any solution with the version 8.2. I know this can be achieved with version 8.5 and above using set analysis
It's true that the associative nature of model restricts the data that we view, but in-order to see YTD data which is outside the associative model you can create a standalone date table which is not linked to any other table in your model. This date table ideally should have all the dates and YTD, MTD, prev year/month etc. Next, you need to use the date from this table in your UI expressions by comparing it with actual date present in the model.
Without a set, you will need to calculate the values in the script into a table that won't be reduced by expected selections - really depending on your UI.
you can also associate your dates with flags - YTD Flag, PYTD Flag, etc... However, those flags will also be ruled out if the user makes date selections.
I understand your concern about large data set, but the only way to achieve your goal without Set Analysis is to pre-calculate the totals in the script or to use flags and restrict (discourage) date selections.
It's true that the associative nature of model restricts the data that we view, but in-order to see YTD data which is outside the associative model you can create a standalone date table which is not linked to any other table in your model. This date table ideally should have all the dates and YTD, MTD, prev year/month etc. Next, you need to use the date from this table in your UI expressions by comparing it with actual date present in the model.
