7 Replies Latest reply on Mar 15, 2018 9:27 AM by Silvia Assuncao

# help If Formula - maybe aggregation

Hi, I'm have some trouble creating this calculation.

In my dimensions I have this company name which have 2 different company codes and I need the formula to consider that when doing the calculation.

when I have the company codes displayed in the table the calculated field is okay, but when I take off the compay code the number changes. I think it's some aggregation problem.

could someone help me out with this?

• ###### 1. Re: help If Formula - maybe aggregation

I have changed your formula little bit, I hope this works.

Please see attached workbook .

sum(IF ([Company_Code])<>"F105"  THEN ([Aop Lc]-[Estimate Lc])/({ include : avg([USD Rate AOP])})

else

([Aop Lc]-[Estimate Lc])

END) 1 of 1 people found this helpful
• ###### 2. Re: help If Formula - maybe aggregation

thanks, it worked. thanks

• ###### 3. Re: help If Formula - maybe aggregation

Hi Arvingarg,

maybe you can help me again.

So I have selected other Company Codes (or countries) and when I have only this field as a dimension in my table the number are total correct.

but then I duplicated this sheet and included 2 new dimensions for category and the values for this formula started to be wrong.

then I duplicated the sheet again to have the values only by category and now the values are even more wrong. what change is necessary for the number to be according to the first sheet?

I'm including the application with the new sheets here.

I would really appreciate your help here. • ###### 4. Re: help If Formula - maybe aggregation

Hey

I looked into this. If you notice Average USD Rate AOP is not matching as well. If variation formula is also using this column as well.

I think it makes sense. Because when you change granularity average changes as well.

I hope below average calculation makes sense.

6+2 /2 = 4     4+6+2/3 = 4    4+4 = 8

6+2+4+6+2/5 = 4

• ###### 5. Re: help If Formula - maybe aggregation

So, how can I solve this granularity problem and have the indicated field to have the same total value as I have in when value is by Countries only?

• ###### 6. Re: help If Formula - maybe aggregation

Sorry, It is very tough to say without knowing more details about this workbook and data.

I will look into this when time permits.

• ###### 7. Re: help If Formula - maybe aggregation

Hi!

thanks for the reply.

I think I found the solution.

I did two things. as I need for the values to consider the fixed rate according to the company name, I created a calculated field to calculate the currency rate average:

I named this field TESTEE_USD_RATE

then I changed the other calculated field, including this field in the formula. now it totally worked.

thanks