Hi, and thank you in advance for having a think about this one! 🙂
We've been experiencing a really unusual issue with a seemingly simple Valuelist in one of our tables and we can't seem to fix it...
The following Valuelist is in place as the second dimension, to enable us to split calculation into current an previous financial year periods:
=ValueList('Current','Previous')
We then have the following expression:
=IF(ValueList('Current','Previous') = 'Current',
Sum( {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vMinDate)<=$(vMaxDate)'}>} [SalesOrderLine Value GROSS]),
Sum( {< $(vIgnoreDateFields), [FirstDate Date]={'>=$(vPreviousMinDate)<=$(vPreviousMaxDate)'}>} [SalesOrderLine Value GROSS])
)
The formulas within the expression work correctly on their own without the Valuelist function.
Now, you'd think that this would give a value (or null/zero) for each Value in the Valuelist, but it does not... with some primary dimension values, it defaults to the Previous value and populates both the Current and Previous values with the Previous. If we change the expression to Pick & Match, we get no values at all, suggesting that the Valulist dimension values do not exist, but only for some of the primary dimension values...
Does anyone have any idea why this might be happening? If this can't be fixed, it looks as though we'll have to do this the messy way and create and expression for each separate value, which is really a last resort...
Jenn
