Calculated Field Creates a lot of rows
Answered
Hello!
The attached picture shows my problem, as soon as I am using a Calculated Field the first column is being divided into multiple instances until I aggregate the column where my Calculated Field is.
In the case of a variance in £, this is not a problem, however, the problem is when I try to calculate the percentage difference.
I'd like to use a Calculated Field because I'd like to have calculated total as well, which is not possible with advanced functions.
The first column is a drill-down column, is that causing that issue?
Best wishes,
Mat
Files:
problem 2.png
The same question 
Hi Mat,
Thank you for reaching out. If you don't want the Calculated Field divided into multiple instances at all, when you're building your Calculated Field, you can simply click on the Σ symbol to do SUM(fieldname1 - fieldname2), which it looks like in your case would be SUM ( "Month - New" - "Month - Old" ).
Let me know if this is may be what you're looking for.
Thanks,
Mike
Hi Mat,
Thank you for reaching out. If you don't want the Calculated Field divided into multiple instances at all, when you're building your Calculated Field, you can simply click on the Σ symbol to do SUM(fieldname1 - fieldname2), which it looks like in your case would be SUM ( "Month - New" - "Month - Old" ).
Let me know if this is may be what you're looking for.
Thanks,
Mike
Hi Mat,
Thanks for your response. Try dividing your new Calculated Field for variance by SUM(Month - Old):
As you can see, my variance percentages are correct (.91, and .81). As the equation for variance percentage is: (# under SUM Invoice Estimate - # under SUM Invoiced Amount) / # under SUM Invoiced Amount = .91 (when set to 2 decimal places). In your case it's ultimately "Month - New" - "Month - Old" / "Month - Old". You just have to do it in this roundabout way using two separate calculated fields as exemplified in this and my previous response.
Let me know how this goes.
Thanks,
Mike
Hi Mat,
Thanks for your response. Try dividing your new Calculated Field for variance by SUM(Month - Old):
As you can see, my variance percentages are correct (.91, and .81). As the equation for variance percentage is: (# under SUM Invoice Estimate - # under SUM Invoiced Amount) / # under SUM Invoiced Amount = .91 (when set to 2 decimal places). In your case it's ultimately "Month - New" - "Month - Old" / "Month - Old". You just have to do it in this roundabout way using two separate calculated fields as exemplified in this and my previous response.
Let me know how this goes.
Thanks,
Mike
Hi Mat,
I'm just going through some of my older cases. I'm going to go ahead and mark this one as Answered, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Hi Mat,
I'm just going through some of my older cases. I'm going to go ahead and mark this one as Answered, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Replies have been locked on this page!