Hi, I am creating a GI and meet an issue. One of my result grids with a COUNT aggregate(A) and another of my result grids is a formula(B). I want to use a formula to divide them to get a percentage result (A/B), how can I do it? Attached with the XML file.
Best answer by LeanneM
Hi Even though you have long formulas you just enclose it with sum, so:
=(Sum(Sales)-Sum(Costs)) / Sum(Sales)
It does work, but just takes a while to run it.
Simply replace the Sales etc with your formulas.
Make sure you have the grouping set and remove the aggregate function as you are using it in your data field
+4You would group by ProjectID in the GROUPING tab and then in the Data field
=Count([POOrder.OrderNbr]) for the B
Then your percentage column is
=(DateDiff(‘d’,[POReceipt.usrReceiptDate],[POOrder.ExpectedDate] / Count([POOrder.OrderNbr]))*100
Schema field for that calculation needs to be a field that has decimal points
That should work
Hi Ibarker,
Thanks for your reply. My situation is a little different in that I am using the SUM aggregate function instead of the count. Basically, in the GP% field, I want it to divide the Gross Profit field by the Revenue field. Both the Gross Profit and Revenue fields are aggregated by SUM. How would I go about doing this?
+4Hi Even though you have long formulas you just enclose it with sum, so:
=(Sum(Sales)-Sum(Costs)) / Sum(Sales)
It does work, but just takes a while to run it.
Simply replace the Sales etc with your formulas.
Make sure you have the grouping set and remove the aggregate function as you are using it in your data field
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
