Select only available value

[#1]

I am trying to show data for current YTD as well as Previous Year. I have my data select-able by a Year and Month dimensions. I currently use set Analysis to get Previous year as such:

=alt(sum({<[Year]={'$(=Max([Year]) -1)'}, [Deduction Detail] = {'FUNDED'}>}[Net Sales Home]),'N/A')

The problem is though, since we only have months 1-5 of 2014, the above formula will give us months 1-12 of 2013 instead of 1-5 of 2013 when so Month selection is made.

Of course, if I select Months 1-5 in 2014 (rather than keep it blank) then the formula works.

FYI: Year and Month are derived from a single field [Year Month], I manipulate that single field into two fields during load, so maybe I could use set analysis to reduce [Year Month] by a year, but that hasn't worked for me.

Ideas?

[jonathandienst · Accepted solution]

Hi

You will need to limit months as well in the set expression - something like:

sum({<[Year]={'$(=Max([Year]) -1)'}, Month={'<=$(=Max({<Year={'$(=Max(Year))'}>} Month))'}, [Deduction Detail] = {'FUNDED'}>} [Net Sales Home])

Alternatively, you could use

Month=P({<Year={'$(=Max(Year))'}>} Month)

HTH

Jonathan

[#3]

I'll try that, but wouldn't "Month={'<=$(=Max({<Year={'$(=Max(Year))'}>} Month))'}" give me only the last month of the max year?, not the 5 available months?

I used the "Month=P({<Year={'$(=Max(Year))'}>} Month)" and it seems to work, I'm just confused on what it really did.

Looked a bit on the forum, and found a quick tutorial on P() and E(): P() &amp; E() and where do you use them?

Again thanks for the tip, I learn something new every day

[jonathandienst]

The first expression would limit the months to less than or equal to the last month of the max year. Note the <= in the expression. But this way assumes that the months are numerical (or dual) values, 1-12. The function Month(date) returns a dual.

The P() -- possible values -- function will work with other month formats, such as month names (strings), which will not work for the first expression.

Jonathan