Hi guys,
I got the following issue:
I want to build a list which shows the top 5 of resellers in a selected month and country and ranking them. So far so good, this works fine. The ranking is based on the sales amount in the chosen month and country.
The first column shows the rank, second column: reseller, third column: sales amount of the chosen month
So, now comes the tricky one, to my mind:
I want to add a fourth column, which shows me the ranking of the shown resellers in the list from the previous month.
At this point I have no idea how to create a data item which says "show me the ranking of the reseller from the previous month"
The modell is a dmr-modell.
Do you have any ideas how to solve this?
If you need any more information, pleas let me know.
regards
McSaesh
Quote from: McSaesh on 10 Apr 2015 12:27:52 AM
Hi guys,
I got the following issue:
I want to build a list which shows the top 5 of resellers in a selected month and country and ranking them. So far so good, this works fine. The ranking is based on the sales amount in the chosen month and country.
The first column shows the rank, second column: reseller, third column: sales amount of the chosen month
So, now comes the tricky one, to my mind:
I want to add a fourth column, which shows me the ranking of the shown resellers in the list from the previous month.
At this point I have no idea how to create a data item which says "show me the ranking of the reseller from the previous month"
The modell is a dmr-modell.
Do you have any ideas how to solve this?
If you need any more information, pleas let me know.
regards
McSaesh
Hi,
If you are using a DMR model, that means you have dimensional expressions available to you, which means finding relative members (eg months) is fairly easy.
Assuming your resellers are lower level members of the same hierarchy your countries come from, you can deliver your country resellers with an expression such as:
descendants([Your Country level] -> ?Country?, [Your Reseller level])
For your sales amount based on Month, you can use an expression such as:
tuple([Your Month level] -> ?Month?, [Your Sales Amount measure])
To deliver the rank, you can use an expression such as:
rank([Your monthly Sales Amount] within set [Your Country Resellers])
Finally, to get the rank of the reseller from the previous month, you can use an expression that utilizes the prevMember() function eg:
rank(tuple(prevMember([Your Month level] -> ?Month?), [Your Sales Amount measure]) within set [Staff Name])
Cheers!
MF.
Hey MFGF,
thanks for your reply. I'm going to try it this way, but can you please tell what you mean with "[Staff Name]" in the last expression you mentioned? Do you mean with staff name the reseller?
thx in advance
McSaesh
Quote from: McSaesh on 13 Apr 2015 01:08:14 AM
Hey MFGF,
thanks for your reply. I'm going to try it this way, but can you please tell what you mean with "[Staff Name]" in the last expression you mentioned? Do you mean with staff name the reseller?
thx in advance
McSaesh
Oops! Sorry - it should be [Your Country Resellers] :)
MF.
Hey MF,
thanks for your quick reply!
I got the report running and cel surprise it works like it should work ;)
So, thanks so much MF for your support! I really appreciate that.
greetings
McSaesh
A hat tip to the muppet for an elaborate answer and an elegant solution!
Quote from: McSaesh on 13 Apr 2015 06:53:43 AM
So, thanks so much MF for your support! I really appreciate that.
Quote from: Nimrod Avissar on 14 Apr 2015 01:50:42 AM
A hat tip to the muppet for an elaborate answer and an elegant solution!
Blush. Thanks for the kind words :)