Calculate and display the percentage between aggregation levels

[nglamvn]

Hello Experts,

I have a requirement to create a KF to calculating and displaying the percentage of a product quantity in compare with the sum quantity of a higher aggregation level (the product group that contains the requested product).

Please advise if any way to do this.

Thank you very much for your time and response.

[AyanBishnu]

nglamvn

Yes you can do this very easily. you will already have a planning level at LOCPROD. Create 1 more Planning level (e.g LOCPRDGROUP).Lets say KF1 is where you have individual product qty. in that KF create one more level of expresseionKF1@LOCPRDGROUP = SUM(KF1@LOCPROD). Then create 1 more helper KF HKF1@LOCPRDGROUP = KF1@LOCPRDGROUPThen create the new KF where you want the percentage to be calculated. Lets say KF2KF2@LOCPROD = KF1@LOCPROD / HKF1@LOCPRDGROUP *100and this will solve your problem.

[nglamvn]

Hi Ayan,Thank you very much for your support. I've tried your suggestion however the KF2 doesn't display on planning view. Please correct me if I did something wrong with your instruction:

1. I created a planning level called WKPRODLOCCUSTPRDGROUP (follow your example as LOCPRODGROUP). This planning level was copied from WKPRODLOCCUST (as your LOCPROD).

2. I used ActualQty KF as your KF1. In this KF I added actualQty@WKPRODLOCCUSTPRDGROUP = SUM(actualQty@WKPRODLOCCUST).

3. I created the helper called HACTUALSQTYRATIOCAL as your HKF1.

4. I created RATIOPRODGROUPLV3 as your KF2 with your suggested expression.

In the planning view, when I called RATIOPRODGROUPLV3, the KF is blank. Please find attach for the screenshot of this KF's calculation graph and the planning view.

Thank you again for your very helpful reply.

calculationgraph.png planningview.png

[AyanBishnu]

the problem in the PL WKPRODLOCCUSTPRDGROUP. you should not have PRDID at that level. it will be prd group that should be the root in the planning level and not prdid

[Praveen_Jadhav]

Hi Lam,

The steps Ayan suggested should resolve the issue. I am elaborating on the steps to follow again:

1. Create a planning level WK-LOC-CUST-ProdGroup (WKLOCCUSTPRODGROUP). The root attributes will be Loc, Cust, and ProdGroup.
2. In the existing ActualQty KF add one more level of expression, ActualQty@WKLOCCUSTPRODGROUP = SUM(ActualQty@WKPRODLOCCUST).
3. Create helper KF: HACTUALSQTYRATIOCAL@ WKLOCCUSTPRODGROUP = ActualQty@WKLOCCUSTPRODGROUP
4. New KF RATIOPRODGROUPLV3 which you have already created should give you a percentage (this should include handling denominator with NULL):IF(ISNULL(HACTUALSQTYRATIOCAL@ WKLOCCUSTPRODGROUP ),NULL, (ActualQty@WKPRODLOCCUST / HACTUALSQTYRATIOCAL@ WKLOCCUSTPRODGROUP)*100)

BR,

Praveen Jadhav