Ranking Data

[Ajay]

Hi All

Apologies if this is a really easy thing to do.

I have a sales cube which simply holds a dimension for customers and a dimension for month. Everyday the cube is updated from the underlying sales system, and it has now become a requirement to look at the top 10 customers everyday.

What is therefore required is how do i within a spreadsheet pull the top 10 customers, based on sales units, from the cube. I started out by slicing the data and then running a macro over the data to sort it and then delete the unwanted rows but this is not really sustainable long-term.

I have also tried to see what i can do using a dynamic slice, but again to now avail.

Any thoughts, greatly appreciated.

Regards
Ajay

[Wim Gielis]

Ajay,

I'm pretty confident that it can be done with an MDX statement. Full details can be found here: http://www.bihints.com/book/export/html/68

But easier will be to set up a view (that you save), do a right mouse button click in the grid of the view, select Filter, after which you filter of Top Count: 5.

[Ajay]

Wim

That is really helpful. I had not realised that you could do this.

If I save a view with the filter set to say topcount = 5, will it always apply the filter when the data changes ?

Can I then save this view as a dynamic slice so that I can then run reports out of Excel ?

Regards
Ajay

[Wim Gielis]

Wim

That is really helpful. I had not realised that you could do this.

If I save a view with the filter set to say topcount = 5, will it always apply the filter when the data changes ?

Can I then save this view as a dynamic slice so that I can then run reports out of Excel ?

Regards
Ajay

2 times affirmative.

[Steve Rowe]

Hi Ajay,
Note that though the filter is dynamic when the data changes it isnt if the selection changes.
If you set the view up so that the filter is running off October Sales and then load November sales and change the view to reference November the filter will not update. You would need to rewrite the filter to reference Novmeber too.
Cheers

[Ajay]

Fantastic, thankyou for help. I shall try this a bit later and let you know if i have any issues

Ajay

[lotsaram]

Ajay

You can't apply a native TM1 filter in a dynamic slice. To do this in an Excel view you need an active form in 9.4 and then it is really easy to set up. However this doesn't mean that you can't achieve a similar result in 9.0, you will just have to use a few tricks. There are a couple of ways to do what you want:

First trick, set up a new consolidation "Yesterday" in your time dimension with a single child (being the previous day or last day with sales results), this can be maintained via TI. Rather that having to continually update filtered views (or subsets) they can then instead be based on this consolidation. (This can get around the issue Steve mentioned.)

1st alternative: Compile the MDX to return your top 5 with VBA in Excel (this is probably the best solution but will require some coding. Depending on your level of VBA and understanding of MDX the required coding could be daunting or trivial or anything in between.) There is more on this here
2nd alternative: Use an in-spreadsheet browser filtered view for your report (limited formatting options but it will work with minimal setup)
3rd alternative: Use a dynamic subset based on a filtered view to drive your top 5 list (more flexible formatting options than using ISB, good option, easy to implement but less flexible than VBA MDX option.)

[Ajay]

Thanks Lotsaram

I have tried it this evening and can see if coming through in Excel using Will's comments from above, but taking onboard your's and Steve's i need to put in place more robust solution.

Moving of 9.0 is not an option presently so i am going to have to give one of the three alternatives a go. At this stage option 2 might work, as i have never used MDX (wouldn't know where to start !) and not good at VBA (except recording a macro!!)

Ajay