5 Replies Latest reply on Mar 10, 2016 1:53 PM by tanya.kanwar

# problem with if statement

I am trying to run this statment and its giving me error :

IF [Site ]='TTC'

then SUM([NET_revenue])/2500

ELSEIF [Site ]='Bay'

THEN SUM([NET_WIN])/450

END

Error:Cannot mix aggregate and non-aggregate etc etc

i went through various blogs to find solution and as per them the solution is to add ATTR in front of site.

However this doesnt work.

I get no values displayed. how do i correct it..?

Little bit background to the problem:

I have 10 sites. for each site I want to calculate the revenue per day per people in that location.

so supposing for site 1 ie TTC for Day1  , revenue = 1.5M and people are 256

so my calculation for day1 should be 1.5 M/256

Day 2 : revenue = 2.1M and people will also be same for each location

so my calculation for day2 should be 2.1 M/256

I am trying to build this query..

# Cannot mix aggregate and non-aggregate

• ###### 1. Re: problem with if statement

Hi Tanya,

try to use this formula.

IF [Site ]='TTC'

then [NET_revenue]

ELSEIF [Site ]='Bay'

THEN [NET_WIN]

END

By default it's aggregated as SUM. If you would like to change it, just click on calculation and change it to AVG or MIN or whatever you want

• ###### 2. Re: problem with if statement

I haven't tried this, but it seems like you could use a level of detail calculation here:

IF [Site ]='TTC'

then {fixed [Site ] :SUM([NET_revenue])}/2500

ELSEIF [Site ]='Bay'

THEN {fixed [Site ]:SUM([NET_WIN])}/450

END

• ###### 3. Re: problem with if statement

It gives me zero values (no result)

• ###### 4. Re: problem with if statement

the key is it divide the revenue/win by a number( 450 or 2500).. this cant be missed..

• ###### 5. Re: problem with if statement

Little bit background to the problem:

I have 10 sites. for each site I want to calculate the revenue per day per people in that location.

so supposing for site 1 ie TTC for Day1  , revenue = 1.5M and people are 256

so my calculation for day1 should be 1.5 M/256

Day 2 : revenue = 2.1M and people will also be same for each location

so my calculation for day2 should be 2.1 M/256

I am trying to build this query..