Removing Residual Value in Summaries
I am trying to make a summary in Monarch Data Prep Studio where I compare debits to credits to make sure they balance. I have a summary that has columns for the account number, the debit value of the account, the credit value of the account (which should be the same value with the opposite sign), and lastly a column made by the summary that sums the debit and credit. What I want to do it look at the sum of the column that sums the debits and credits and make sure the whole thing goes to 0 thus proving that all of those accounts balance. Unfortunately, when I sum the debits and credits I get a small, non-zero residual value. How can I configure the summary to round very small values? When I sum 8147.22 and -8147.22 I should get 0, not -1.81898940354586E-12. When I go to edit the sum column in measures Monarch Data Prep Studio it does not give me the option to use a custom formula; I can only select from their list of aggregations. In Monarch Classic making a summary gives you the option to round calculated fields to a certain number of decimal places; this functionality would solve my problem if only I could find it in DPS.
Answers
-
Hi Daniel,
By default Monarch goes out many decimal places to conform to a mathematic standard. A lot of our customers are doing work similar to yours where it's dollars & cents and does not need to go beyond 2 decimal places. As is always the case with Monarch, there's more than one way to address this. You can right-click or go to the column's drop down menu, choose Compute, then Round with Precision and set to 2. You can also access the same drop down, choose Format and Currency.
If you need any further assistance with this workspace please don't hesitate to reach out. It sounds like work our technical team has been involved in many times in the past and would be happy to review with you.
Best regards,
Dylan Tancill
0 -
Hi Daniel,
Nice to meet you. One approach is to change the format of all your value columns to either currency or financial, that will resolve it.
Check out the video answer to Exercise 6 in our Monarch Success Plan. It is an exact representation of what you’re trying to accomplish. The last 2mins of the 13min video directly answer your question.
Lastly, what systems of record are you working with? Have you seen our Monarch Model Library?
It’s a complimentary list of pre-built models and workspaces for many different systems, you may see some templates of interest to you there.
Best Regards,
Baba
0 -
Dylan_20833 said:
Hi Daniel,
By default Monarch goes out many decimal places to conform to a mathematic standard. A lot of our customers are doing work similar to yours where it's dollars & cents and does not need to go beyond 2 decimal places. As is always the case with Monarch, there's more than one way to address this. You can right-click or go to the column's drop down menu, choose Compute, then Round with Precision and set to 2. You can also access the same drop down, choose Format and Currency.
If you need any further assistance with this workspace please don't hesitate to reach out. It sounds like work our technical team has been involved in many times in the past and would be happy to review with you.
Best regards,
Dylan Tancill
The data in the table I am making a summary with already has the debit and credit values formatted as financial and rounded with precision to 2 decimal places. When I make the summary I am still getting very small numbers in scientific notation when I should be getting 0. In the Analyze tab where the summaries are made I cannot right click the column and format it there. All that comes up is the option to search or copy from that column. The formulas allowed in measures do not allow the Round() function. What are the other ways I can address this?
0 -
Dylan_20833 said:
Hi Daniel,
By default Monarch goes out many decimal places to conform to a mathematic standard. A lot of our customers are doing work similar to yours where it's dollars & cents and does not need to go beyond 2 decimal places. As is always the case with Monarch, there's more than one way to address this. You can right-click or go to the column's drop down menu, choose Compute, then Round with Precision and set to 2. You can also access the same drop down, choose Format and Currency.
If you need any further assistance with this workspace please don't hesitate to reach out. It sounds like work our technical team has been involved in many times in the past and would be happy to review with you.
Best regards,
Dylan Tancill
I have found the answer. Format changes do not affect summaries that are already made even if you right click the underlying table and select refresh. You must delete the summary and remake it. Then the changes to the format of the underlying data will take affect. Thanks for your help.
0
Altair Employee