6 Replies Latest reply on Nov 13, 2013 5:21 AM by ravi.vankayala

# Need help on comparing  2 calculations for conditional formatting

Hi all,

I have a requirement where i need to compare target value with actual value and show different colors.

target value contains a number and actual value contains percentage numbers.

Now i need to compare both and show some different colors based on the condition.

problem i am facing is as we cannot mix aggregate and non aggregate functions i have added attr in target value calculation but i am not able to get correct value.

any idea to compare both values ?

• ###### 1. Re: Need help on comparing  2 calculations for conditional formatting

Hi Krish, It is really difficult to understand the problem without seeing the data and calculation. Can you upload some sample data or a packaged workbook?

• ###### 2. Re: Need help on comparing  2 calculations for conditional formatting

hi indumon,

sorry i couldn't get packaged workbook but i will explain what i meant

one calculation say is done  x=ZN(([DAYS (BB)]))/ZN(COUNT([DAYS])) this returns some percentage

another calculation is done like y=

IF [column1] = 'value' then [column2]    this returns a value

END

when i am trying to do x-y i get a error as cannot mix aggregate and non aggregate arguments in function

so i added attr for x calculation but i am not getting correct value

IF attr(y)>=15 then 'green'

elseif x <=0.02 then 'yellow'

else 'red'

• ###### 3. Re: Need help on comparing  2 calculations for conditional formatting

Hi Krish,

As per your calculation of X is aggregated using Count(), but y is not aggregated. Use sum, avg .. any appropriate aggregate function for y to do X-Y. then it should work.

• ###### 4. Re: Need help on comparing  2 calculations for conditional formatting

hi indumon,

i added attr but data is not coming correctly. using the same calculation i am trying changing the color of the line using some conditions but it is not working correctly

• ###### 5. Re: Need help on comparing  2 calculations for conditional formatting

Its just like a guessing game.

Can you mock up some data if you cannot attach packaged workbook. so we can help you better.

• ###### 6. Re: Need help on comparing  2 calculations for conditional formatting

will try to get some data