How to arrive in this kind of report?

[Loed]

Hi guys,

I have a question on how can I get this kind of report. Is this possible in query designer or in the back-end?

Here is my final report format per MONTH:

But in order for me to tag a material as class A, B, or C, I need to do some computations as shown below.

First problem is, how can I sort the MATERIAL per GROUP based on their AMOUNT in DESCENDING ORDER? I'm thinking of creating a CONDITION of TOP N (N = 100,000 to be safe since we have thousands of materials) with reference to MATERIAL and GROUP. I did not try this yet but do you guys think this will work as shown above?

EDIT:

Tried using the CONDITION but it is not sorting the AMOUNT based on the MATERIAL NUMBER per GROUP. Maybe there is other way to achieve this?

EDIT:

The other problem is, how can I compute the NUMERATOR part? There is a CUMULATED option in the CALCULATIONS tab of a key figure in query designer but as far as I know it is only for DISPLAY PURPOSE. So even if I can see the CUMULATED value along the column, I still cannot compute the % part since the values shown is not the real value. In short, using it will not work. How do you guys think I can achieve the NUMERATOR part? I already have an idea about this but I need to do some back-end work. I'm trying my luck and hoping if this can be achieved in the query designer or if I can get simpler solution. 🙂

Amount = this is my key figure (CAN BE DONE)

Numerator = this is the cumulative value of all materials per group starting from the highest to the lowest value of amount

Denominator = this is the total amount of all materials per group (CAN BE DONE).

I think I can do the denominator part using CONSTANT SELECTION.

% = Numerator / Denominator (CAN BE DONE)

Class = material will be tagged as class A if it is part of the TOP 80%; class B if it is part of 80% - 95%; and class C for other 5%. (CAN BE DONE)

I think I can do the CLASS tagging using CELL. But if you have other suggestions, please say so. I did not try it yet since my problem first is how to compute the NUMERATOR part. 😄

Thank you so much.

Loed

[kohesco]

create formula Numerator= [amount] but set cumul flag on in calcutation tab

[Loed]

Hi Koen,

CUMULATIVE flag on the CALCULATIONS tab is for DISPLAY purpose only. I can't use it in the calculation of any formula. So in this case, I can't use it in my % formula. Did you make it work in any formula when ticking the CUMULATIVE part?

Thank you.

Loed

[kohesco]

Yeah I reckoned you would say that 🙂

I have tried this and I think it looks good:

setup: Groep & Material Group (> show result rows)

Amount= first column of key figures

Nominator you can calculate it as in my previous post, but you are right it is useless in the whole calcul setup

Denominator is set as SUMCT 'Amount'

% is Amount %A Denominator

% Cumul is % with the cumul flag on

Can you try this?

[Loed]

Hi Koen,

I tried it. But how will I know which material will be part of CLASS A-B-C or 1-2-3?

Thanks.

Loed

[kohesco]

Yeah, I just got the exceptions colouring on it, like you said you can't do anything via/with the cumul, would that be okay for you, it is a nice visualitzation though 😉

[Loed]

😄

What's the setting of your EXCEPTION? I will give it a try. 😉

[Loed]

I think I got your idea. So 3 colors for 0 to 80, 80 to 95, and 95 to 100? If yes, I found an error in the design. 😄

Your idea will work IF and ONLY IF I can sort the MATERIAL PER GROUP in DESCENDING order based on their AMOUNT. However, the query sorts the data in ALPHABETICAL ORDER or ASCENDING ORDER based on the layout of the characteristics, NOT BASED in the key figure. Do you have a way to do this SORTING?

[kohesco]

of course,

create a condition on amount and set TOP % = 100

set the condition active and it will sort your report neatly

[Loed]

Hi Koen,

Already tried using the CONDITION TOP N but it is not sorting the AMOUNT based on the MATERIAL NUMBER per GROUP. It did sort the data by AMOUNT in DESCENDING order but as a whole and not by MATERIAL PER GROUP. Did you make it work?

Regards,

Loed

[kohesco]

Hi, I did nothing special it starts over for every group

[Loed]

Maybe they are in alphabetical order or in ascending order? Are they dummy data? Because I also did the TOP N in my query but I wasn't able to achieve my desired sorting.

Regards,

Loed

[kohesco]

No, the condition is sorting the key figure and because the material group and material are in the rows, it does it for each group as well, like you wanted. In the condition have no special setting applied, so it is basic behavior. I used a random BW-Cube. Make sure your condition is active!

for instance

https://archive.sap.com/discussions/thread/1348789

[Loed]

Thanks Koen for the idea. 🙂

But I think I still need to do a back-end development since I need to transfer the value A,B,C in one of the attributes of MATERIAL object.

Do you have an idea about the ABC classification? tcode RSDMWB?

Regards,

Loed

[kohesco]

sorry never worked with that, sure you'll find it

[Loed]

Thanks mate.

Loed

[kohesco]

Maybe better to tag the question in SAP BW instead of SAP BEX

[Loed]

I already made this work by doing the config in the back-end. I'm just trying my luck if I could also find a way in doing this in the report level. The ABC classification is also one of the ways to do this but I can't make it work when using 2 fields. But using it in 1 field, ABC classification is working great. Thanks to Anand for the idea, didn't know that it exists in BW. LOL! There are also some other tools in RSDMWB, I will also try to study them. Have a look at it Koen and tell me you made it work with 2 fields. 😄

Regards,

Loed

[kohesco]

Hi,

if you can convince the business to use 1 - 2 - 3 in stead of A - B - C ... then you just need a calculated KF to have your result

[Loed]

Hi Koen,

How will I do that 1 - 2 - 3? Is the NUMERATOR part possible?

Thank you.

Loed

[kohesco]

Hi,

for example formula beneath

([kf percent] > 0) + ([kf percent] > 80) + (kf percent] > 95)

If percentage = 99 your category will be 3

(1+1+1)

If percentage = 83 your category will be 2

(1+1+0)

if percentage = 43 your category will be 1

(1+0+0)

[Loed]

Hi Koen,

Which KF percentage are your referring? Is it the % in my sample above? But I can't get % if I don't have the values of my NUMERATOR. Do you also have an idea how to get the NUMERATOR part?

Regards,

Loed

[Loed]

Hi Anand,

Here are the steps I did.

First.

RSDMWB to create the model.

I saw these in the blog you have posted.

1. Classified Object – In our example its Customer ID
2. Classification Criterion – Revenues generated by customers.
3. Grouping Attribute – where to store result.

So I did assign the ff objects:

1. Classified Object – GROUP and MATERIAL NUMBER
2. Classification Criterion – AMOUNT
3. Grouping Attribute – I DID NOT USE THIS. I can't see the purpose of using the grouping attribute since I already can use the ABC CLASS object in the APD. I tried including and excluding this type but I did not notice any difference in the result. Can you explain me the real purpose of this type? Do I really need to add it in my ABC MODEL?

***I used Cumulated Percentage of Classified Object since I wanted to consider both of my classified objects..

A from 0 to 80 CLOSED

B from 80 to 95 OPEN ON LEFT

C from 95 to 100 OPEN ON LEFT

Second.

Created the APD.

QUERY -> ABC CLASS -> MASTERDATA

***I will be using the result of ABC class as the value of the CLASS infoobject, which is an attribute of MATERIAL NUMBER..

***My query contains GROUP and MATERIAL NUMBER in ROW and AMOUNT object in COLUMN..

Did I miss something?

Thank you.

Loed

[Anand71]

Hi,

Search for ABC Classification . Use T-Code RSDMWB and cread model and use in Analysis Process Designer(RSANWB)

https://blogs.sap.com/2005/08/10/customer-prioritization-using-abc-classification-model/

Regards,

Anand

[Loed]

Hi Anand,

I tried to use the ABC classification. However, it's not working for 2 or more reference characteristics? Is that correct? Or did I miss something?

In the blog that you have posted, it is only using CUSTOMER ID. In my case, if I only use MATERIAL, the ABC classification is working. However, I also need to consider the GROUP infoobject. How will I make it work? I already tried to have 2 CLASSIFIED OBJECT (material and group) but it is not working properly.

Any help?

Thank you.

Loed