Problems understanding and using Only(Year)

[#1]

I have a dataset that includes information for two years, 2011 and 2012.

It is nothing more than a list of weekly production volumes - that is Year, Week, Litres Produced.

The following are selected elements of the data.

	2011	2012
Maximum Volume	714,214	644,428
Minimum Volume	887	398,564
Total volume	24,263,544	8,026,290

In an attempt to check it works, I have created a field called OY with the expression Only(Year) and, as I had hoped, this returns 2011 for all the 2011 week lines and 2012 for all the 2012 week lines.

My problem:

I need to show the total volume for the applicable year on each week line (so 2011 total for 2011 week lines and 2012 total for 2012 week lines).

Using Set Analysis - and a lot of trial and error, because this is NOT very intuitive!!! - I have managed  to achieve the following returns for each week line in the appropriate year.

	2011	2012
Max({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])	644,428	644,428
Min({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])	398,564	398,564
Sum({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])	8,026,290	8,026,290
Max({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])	714,214	714,214
Min({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])	887	887
Sum({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])	24,263,544	24,263,544

However when I try to use Only(Year), I get this…

	2011	2012
Max({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])	NULL	NULL
Min({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])	NULL	NULL
Sum({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])	0	0

So, there is CLEARLY something I do not understand about Only(Year) as this is making no sense to me and driving me up the wall.

Can anyone save my sanity?

Many thanks.

[swuehl · Accepted solution]

Your set expression and this part: $(=Only(Year))

will be evaluated once per chart, not per dimension value.

So, if you have not selected any Year, only(Year) will return NULL, since you've still got two values and only() function requires a unambiguous value returned.

You could either replace your set expression with an if() clause or use an if() clause to check the year in your expression and then use two set expressions with the year set fixed.

Hope this helps,

Stefan

[#3]

Thank you Stefan,

That is a seriously big light-bulb moment for me!!!

I stil need to get my brain around the if() clause, but I think I can cope with.

Muchas gracias!!!