2 Replies Latest reply: Feb 22, 2012 11:13 AM by Dave Wright

# Problems understanding and using Only(Year)

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({\$} TOTAL [Litres Produced]) 644,428 644,428 Min({\$} TOTAL [Litres Produced]) 398,564 398,564 Sum({\$} TOTAL [Litres Produced]) 8,026,290 8,026,290 Max({\$} TOTAL [Litres Produced]) 714,214 714,214 Min({\$} TOTAL [Litres Produced]) 887 887 Sum({\$} TOTAL [Litres Produced]) 24,263,544 24,263,544

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

 2011 2012 Max({\$} TOTAL [Litres Produced]) NULL NULL Min({\$} TOTAL [Litres Produced]) NULL NULL Sum({\$} 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.

• ###### Problems understanding and using Only(Year)

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

• ###### Problems understanding and using Only(Year)

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!!!