8 Replies Latest reply on Apr 7, 2013 7:53 PM by Loshini Fernando

    Show most recent year's data

    Loshini Fernando
    Loshini Fernando Apr 4, 2013 4:03 PM

       

      Hi,

       

      I am doing an analysis on a cube data and have come across a problem.

       

      What I would like to do is to show the measure, percentage agreement- the latest year's figure of an organisation.

      For eg: If an organisation has participated in 2007, 2009, 2012 and it's percentage agreement rates are 97%, 95% and 90% respectively, I want to show the 2012 Percentage agreement figure only in the graph.

       

      There's no need to show the year it corresponds to. But only to show organisation's latest year's percentage agreement rate.

      Could you please advice me how to do this analysis? I have attached a screen shot of the graph- how it looks now.

      • 102 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Show most recent year's data
          Joshua Milligan
          Joshua Milligan Apr 6, 2013 10:32 AM (in response to Loshini Fernando)

          Loshini,

           

          I've attached a workbook that mirrors what you are trying to do.  Here are the steps I believe you'll take (based on your image):

           

          1. Create a calculated field named First with the code First() = 0

          2. Place this field in the Filters

          3. Right click the field and select "Edit Table Calculation"

          4. Select "Advanced" for Comput Using

          5. On the Advanced screen move the fields to the right and order them like this:

           

          Organization ID

          Collection Year

           

          6. Also on the Advanced screen, select Field for Order Along, choose "Collection Year", "Minimum" and "Descending".  Click OK

          7. Back on the Table Calculation screen select At the level: "Collection Year" and Restart every: "Organization ID"

          8. Click OK.

          9. Filter where the result is True.

           

           

          Hopefully between the example workbook and the steps outlined above you can get it working for your data.  If not, please let me know.  I'd be more than happy to answer any questions you might have.

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Show most recent year's data
            Joshua Milligan
            Joshua Milligan Apr 6, 2013 10:28 AM (in response to Loshini Fernando)

            And, if you don't want to show the Year as column headers, you can move it to the Level of Detail (the field still needs to be active in the view for the table calc to work.)

            • 3. Re: Show most recent year's data
              Joshua Milligan
              Joshua Milligan Apr 6, 2013 10:31 AM (in response to Loshini Fernando)

              Or, here it is using Year as a label.

               

              Capture.PNG

              • 4. Re: Show most recent year's data
                Loshini Fernando
                Loshini Fernando Apr 7, 2013 6:06 PM (in response to Joshua Milligan)

                Thank you very much Joshua for your support. I appreciate it very much.

                I tried the steps you've instructed. However, In the 6th step, to my surprise, I couldn't see "Collection Year" for me to select under the "Order Along" fields. Please find the screen shot attached which depicts this.

                 

                All the fields that are available under "Order Along" field list are the "Measures" , where as "Collection year" is a dimension.  Is this the reason why I cannot see "Collection Year" come up under the "Order along" fields?

                 

                I some how managed to get across my problem by adding a calculated member "Latest Year" and adding an MDX query (TAIL(NONEMPTY({NULL:[Collection Year].[Collection Year].CURRENTMEMBER} * [Measures].[Percentage Agreement])).item(0)*100) . Please find the figure 2 in the attached sceen shot.

                 

                Nevertheless, I am keen to learn a new way to do my task. So, please let me know what's has gone wrong.

                 

                Thank you again.

                Loshini

                • 5. Re: Show most recent year's data
                  Joshua Milligan
                  Joshua Milligan Apr 7, 2013 7:25 PM (in response to Loshini Fernando)

                  Loshini,

                   

                  I'm not sure why it wouldn't show up.  It could be a limitation of using a cube.  I'll have to defer to the rest of the community as to why you would see this behavior.

                  • 6. Re: Show most recent year's data
                    Andy Harris
                    Andy Harris Apr 7, 2013 7:35 PM (in response to Loshini Fernando)

                    Loshini,

                     

                    I think this is a limitation with cube data sources - the expectation is that calculations have already been done within the cube itself.  I think writing calculated member statements is the only option available - unless anything changed with cube connectivity in v8.

                    • 7. Re: Show most recent year's data
                      Loshini Fernando
                      Loshini Fernando Apr 7, 2013 7:51 PM (in response to Joshua Milligan)

                      Thank you very much Joshua. Had we able to implement the calculation you mentioned, it would have been great, since the other option needs MDX understanding.

                      • 8. Re: Show most recent year's data
                        Loshini Fernando
                        Loshini Fernando Apr 7, 2013 7:53 PM (in response to Andy Harris)

                        Thank you very much Andy for your reply. I reckon it's a limitation in the cube too.