show recently added elements in subset.

Post Reply
escape18in
Posts: 26
Joined: Tue Feb 03, 2015 10:22 am
OLAP Product: IBM Cognos BI
Version: 10.2
Excel Version: Excel 2010

show recently added elements in subset.

Post by escape18in » Wed May 16, 2018 12:31 pm

Hi All ,

i have dimension which have elements lets say
1
2
3.

Now after load 2 more elements is added
1
2
3
4
5

Now in a worksheet i want to show only those recently added elements.how can i achieve this? i have tried with index sort like {Subset ( {TM1FILTERBYLEVEL( {TM1SORTBYINDEX( {TM1SUBSETALL( [Dimension] )}, DESC)}, 0)}, 1, 25)} but it is not working as TI assigning Index randomly. i have hierarchies in dimension as well.

tm123
Posts: 113
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: show recently added elements in subset.

Post by tm123 » Wed May 16, 2018 12:36 pm

Why don't you add an attribute to the dimension and use it for Filtering or SOrting? You cannot rely on the Element Index since it changes

escape18in
Posts: 26
Joined: Tue Feb 03, 2015 10:22 am
OLAP Product: IBM Cognos BI
Version: 10.2
Excel Version: Excel 2010

Re: show recently added elements in subset.

Post by escape18in » Wed May 16, 2018 12:44 pm

tm123 wrote:
Wed May 16, 2018 12:36 pm
Why don't you add an attribute to the dimension and use it for Filtering or SOrting? You cannot rely on the Element Index since it changes
How attribute will identify the new element? it is not necessary in source data that new elements will come at last if i assign attribute as incremental numbers it will not work. i was assuming for any new element TM1 assign index in incremental order.

tm123
Posts: 113
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: show recently added elements in subset.

Post by tm123 » Wed May 16, 2018 12:50 pm

escape18in wrote:
Wed May 16, 2018 12:44 pm
tm123 wrote:
Wed May 16, 2018 12:36 pm
Why don't you add an attribute to the dimension and use it for Filtering or SOrting? You cannot rely on the Element Index since it changes
How attribute will identify the new element? it is not necessary in source data that new elements will come at last if i assign attribute as incremental numbers it will not work. i was assuming for any new element TM1 assign index in incremental order.
I don't understand your question maybe, but if you want to have an attribute that keeps the Element Inserting Order, you don't need to have this from data source but you can achieve this in the TI process. The simplest way is to use DIMSIZ but that will count consolidated elements also, and I am not sure if you want that. But there are more elegant ways of doing this, still in TI.

escape18in
Posts: 26
Joined: Tue Feb 03, 2015 10:22 am
OLAP Product: IBM Cognos BI
Version: 10.2
Excel Version: Excel 2010

Re: show recently added elements in subset.

Post by escape18in » Wed May 16, 2018 12:57 pm

tm123 wrote:
Wed May 16, 2018 12:50 pm
escape18in wrote:
Wed May 16, 2018 12:44 pm
tm123 wrote:
Wed May 16, 2018 12:36 pm
Why don't you add an attribute to the dimension and use it for Filtering or SOrting? You cannot rely on the Element Index since it changes
How attribute will identify the new element? it is not necessary in source data that new elements will come at last if i assign attribute as incremental numbers it will not work. i was assuming for any new element TM1 assign index in incremental order.
I don't understand your question maybe, but if you want to have an attribute that keeps the Element Inserting Order, you don't need to have this from data source but you can achieve this in the TI process. The simplest way is to use DIMSIZ but that will count consolidated elements also, and I am not sure if you want that. But there are more elegant ways of doing this, still in TI.
i will assign numbers in attribute from TI only.but how i identify that 2 elements have recently added which was not there previously. i have a requirement that i have a input cube with 100s of records. in those records some null as well. what user asked is he wants to see only those records which is recently added not all (including nulls) so that it is easier to him to put some values instead of searching in a cube.

User avatar
tomok
MVP
Posts: 2481
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: show recently added elements in subset.

Post by tomok » Wed May 16, 2018 1:35 pm

What is your definition of "recently added elements"? Those added overnight from an automated process? Those added by a user since the last time the cube was checked? It's a lot more involved than your simple question. The best way to do this would be to add an attribute on this dimension, as already suggested, and use that attribute to filter via MDX. If it was me I would have an attribute called "Date Added" and populate it with the date and time (numeric, using NOW()) in the TI process when the element gets added. You can then have a cube view or reports that filter based on specific dates.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Post Reply