Good morning,
There's an issue in my Qv document when I compare Current Period Sales vs. Previous Period Sales in some corner cases where my solution (simple and straightforward) doesn't work.
Current Period may be selected by the user freely and may be non-contiguous dates, YTD, and even contiguous dates across two consecutive years mainly for
moving annual total (MAT). Loaded data in the complete model goes from 2009 to 2012.
My solution involves creating a calendar (granularity up to months, not days) where I add a dimension called 'Previous Year&Month' whose value is exactly 12 months before current date. This way I tie current date to 12-months-before date.
And when I want to get sum(Sales) from previous period (not always a 12 months period nor even same months from previous year) the expresion is quite simple: sum({$<CURRENT_YEAR_MONTH=P(PREVIOUS_YEAR_MONTH),CURRENT_YEAR=,CURRENT_MONTH=>}Sales)
It works perfectly if data from ALL current period exist ut if we have "holes" in some months where some data exist from previous period, then data from previous period don't get included in sum() and results from previous period is wrong.
I've tried different approaches to solve this issue: From using 'getfieldselection' in p() (not very elegant and forces to use 'replace' in my expressions catalog in the script to avoid $-expansion annoyances) to 'selectively unselect' dimensions in set analysis '<...DIMENSION=,...>' but it may be quite a mess if there's too many dimensions.
One of my constraints is that I want the user use only year selector and month selector to select months & year (TAM is indirectely selected through button --> then correct year&month data range is automatically calculated, so user doesn't have direct access to year&month concatenated dimension)
Any idea?
I've attached a simple example which illustrates the problem
Thanks in advance,
Raúl
