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

    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({$<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.