
1. Re: help If Formula  maybe aggregation
arvindgarg Mar 12, 2018 1:36 PM (in response to Silvia Assuncao)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)

IF_AND_AGGREGRATION_2.twbx 622.1 KB


2. Re: help If Formula  maybe aggregation
Silvia Assuncao Mar 13, 2018 6:01 AM (in response to arvindgarg)thanks, it worked. thanks

3. Re: help If Formula  maybe aggregation
Silvia Assuncao Mar 14, 2018 9:39 AM (in response to arvindgarg)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.

IF_AND_AGGREGRATION_2.twbx 691.1 KB


4. Re: help If Formula  maybe aggregation
arvindgarg Mar 14, 2018 12:40 PM (in response to Silvia Assuncao)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
Silvia Assuncao Mar 14, 2018 12:47 PM (in response to arvindgarg)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
arvindgarg Mar 14, 2018 1:20 PM (in response to Silvia Assuncao)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
Silvia Assuncao Mar 15, 2018 9:27 AM (in response to arvindgarg)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