Applying multiple, but separate, selections on one dimension using set analysis
Hello-
This should be a simple question for a lot of you: I'm trying to display the same metric on dimension = [Fiscal Year] for three years in one chart, but the metric's denominator relies on [Total Expenses] from the previous year. So, 2013's metric uses 2012's expenses, 2012's metric uses 2011's expenses, etc. Below is what I have so far--I'd really appreciate any guidance anyone has to offer. Thank you in advance!
-----
(sum(
{<
[Fiscal Year] = {2012, 2013, 2014}, ***this simply selects the three years for the numerator
[YTD]={'Already YTD'} ***this ensures that [Liquid Assets], the numerator, does not get converted to MTD
>}
[Liquid Assets])
/
sum(
{$<
YTD={'YTD'}, ***this normally aggregates MTD values to YTD values, but seems to be causing problems in this expression
Quarter={4}, ***this normally ensures that year-end values are considered; I currently have the [YTD] modifier commented out and this as not selecting anything
[Fiscal Year]={$(=Only([Fiscal Year])-1)}, ***this only works when I have a [Fiscal Year] selected
[Fiscal Year - Quarter]= ***this de-selection ensures that multiple [Fiscal Year] and [Quarter] combinations can be referenced
Set Analysis won't work in this case. Set Analysis modifiers run once for the entire chart.
What you want is the above() function to obtain the [Total Expenses] fro the previous row (dim value). Check out above in the help and you'll find some useful examples here on the forums.
