Applying multiple, but separate, selections on one dimension using set analysis

Wed, 29 Jan 2014 23:50:01 GMT

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

[Total Expenses])

Re: Applying multiple, but separate, selections on one dimension using set analysis

rwunderlich — Thu, 30 Jan 2014 00:18:00 GMT

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.

-Rob

topic Re: Applying multiple, but separate, selections on one dimension using set analysis in QlikView

Applying multiple, but separate, selections on one dimension using set analysis

Re: Applying multiple, but separate, selections on one dimension using set analysis