By using this website, you agree to our Terms of Use (click here)
I have a 12 month P&L. I have several rows in my row set that are % of Sales. When I do my calculation to add the 12 month total it is displaying a sum of those % instead of a percent. So 6.65% instead of 55%. Any thoughts on how to correct this?
Hi Brian,
Try something like this on the Row Set:
=IIf(@0210=0,0,@0320/@0210)
And something like this on the Column Set:
=IIf(@RowCode='0345',(B0320+C0320+D0320+E0320+F0320+G0320+H0320+I0320+J0320+K0320+L0320+M0320+N0320)/(B0210+C0210+D0210+E0210+F0210+G0210+H0210+I0210+J0210+K0210+L0210+M0210+N0210),B+C+D+E+F+G+H+I+J+K+L+M+N)
Not the prettiest thing and there's probably an easier way to do this, but that's what I came up with.
Basically, we're just testing to see if the row is a specific row code. If it is, then we do the calculation ourselves. If it's not, then we do our normal summing up of the columns.
Hi all
I had nine such ratios in a report. As you could imagine the formulas in Tim's excellent approach would have gotten very long. I was not sure if there was a limit to the length of the formulas (does anyone know the limit?), so I used a somewhat similar approach (based on Tim's approach), but I used Printing Controls to Merge the Total Column with a Hidden Ratio Column. The result was much simpler (well at least shorter) formulas.
Row Set:
- On the rows where a ratio was required I used a formula like
=@0465/VALUE(@BaseRowCode, @ColumnCode).
Note no need to check for zero. Changed the dividend according to the ratio/line.
Column Set:
- Total Column: Used the formula
=IIf( @RowCode IN ('0233', '0305', '0415', '0425', '0447', '0467', '0475', '0496', '0560'), Null, B+C+D+E+F+G+H+I+J+K+L+M+N+O+P+Q+R+S)
Set the Printing Control to Merge Next - Ratio Column. Used the formula
=IIf( @RowCode IN ('0233', '0305', '0415', '0425', '0447', '0467', '0475', '0496', '0560'), Switch( @RowCode = '0233', T0230, @RowCode = '0305', T0300, @RowCode = '0415', T0414, @RowCode = '0425', T0420, @RowCode = '0447', T0446, @RowCode = '0467', T0465, @RowCode = '0475', T0470, @RowCode = '0496', T0495, @RowCode = '0560', T0550) / Value(@BaseRowCode, 'T'), Null)
Still not the prettiest on the block, but it works. Here in Australia, we have reported the problem to MYOB who have escalated the problem to Acumatica. Hopefully, they will build in a simpler remedy at some release in the future.
Hello all
Another approach is instead of using a Calc Type column is to use a GL Type column where the DataSource is across the 12 months required.
Thanks so much for sharing this approach jlpatch.
I especially like the use of @BaseRowCode.
Thanks. I will give this a shot when I get time. Still in the middle of an implementation so time is scarce.