9 Replies Latest reply on Apr 4, 2018 9:00 AM by Colleen Reynolds

# Formula question

Hi, I am trying to calculate a ratio using 2 different lines from a table.   In the example attached, I am trying to create a percentage which takes the Feb column - count of loan number 26 divided by Grand Total of 90+Delq amount of  849.   March would be 41/820 etc. I am not sure the best way to write a formula to use to pull out just these 2 amounts for each column to arrive at the monthly percentages. I would appreciate any suggestions. Thank you.

Message was edited by: Colleen Reynolds reattached after extracting data source

• ###### 1. Re: Formula question

Pl extract your data and reattach.

• ###### 2. Re: Formula question

I reattached - thank you

• ###### 3. Re: Formula question

Hi Coleen,

There are a few ways you could get the ratio you are looking for.  Below is one way using LOD.  Let me know if you have any questions

Regards,

Ivan

{ FIXED [Eom Date] : COUNT(IF [Complete Package Same Month] = '1' THEN [Loan Number] END)}/{ FIXED  [Eom Date] : SUM([90+Delq])}

• ###### 4. Re: Formula question

Hi Ivan, Thank you for your response. Your recommendation seems to work on the small sample file I provided but when I try to use it in my production tables which are linked to a much large data set, it doesn't work.   When i break down the numerator and denominator of the ratio in production both amounts are about 3 times the numbers they should be so the final ratio is off.  I don't have much experience working with this type of formula.   Do you know what might be causing this variance? If not, do you have any other formula suggestions? Thank you again.

• ###### 5. Re: Formula question

Ivan, I just figured out the issue. In production I need to filter by additional factors to arrive at the ratio. As an example, I have Company A,B and C and if i use your formula the ratio will only provide a total of all 3. When i try to filter by Company the formula doesn't adjust for just Company A as an example. Any suggestions on how I can use a formula to accommodate for this type of filter?  Thanks

• ###### 6. Re: Formula question

I just read online that Filters are ignored if "Fixed" is used so I changed the formula to "Include" instead.

It appears to work now. If anyone with LOD experience could confirm I would appreciate it.

{ include[Eom Date] : COUNT(IF [Complete Package Same Month] = '1' THEN [Loan Number] END)}/{ include [Eom Date] : SUM([90+Delq])}

• ###### 7. Re: Formula question

You can Keep FIXED and Just add all your FILTERS to CONTEXT

• ###### 8. Re: Formula question

Thank you. That seems to work as well but I am not sure why I would use Fixed with Filters to Context as opposed to Include. Is there something wrong with using Include in this scenario?

• ###### 9. Re: Formula question

FIXED is Evaluated at TOP Level of Order of Operations, I just don't prefer INCLUDE until it is really Needed. Basically they Return Same, Difference is the Level at Which they are evaluated. FIXED  appears to me more Useful. It's my view.

Thanks

Deepak