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?
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?
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. :-D
Thank you so much.