Skip to Content
0
Jan 26, 2018 at 09:07 AM

How to arrive in this kind of report?

166 Views Last edit Jan 29, 2018 at 09:11 AM 3 rev

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. :-D

Thank you so much.

Loed

Attachments

delete.jpg (466.5 kB)
delete.jpg (268.1 kB)