-
1. Re: Show most recent year's data
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
-
Last Year Only.twbx.zip 215.3 KB
-
-
2. Re: Show most recent year's data
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 Apr 6, 2013 10:31 AM (in response to Loshini Fernando) -
4. Re: Show most recent year's data
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
-
In the Advanced Screen.doc 172.5 KB
-
-
5. Re: Show most recent year's data
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 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 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 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.