3 Replies Latest reply on Nov 11, 2012 11:52 AM by Derk Busser

# How to treat this as a constant? Or something... Mixing agg/non-agg problem

I work off of a Daily Average Users number, and I have users per day. I can roll up these very easily for aggregated dates (Y, Q, M, W are all consistent), but not by day of week.

SUM([DAU]) / 52 gives me numbers extremely consistent with the date-aggregated rollups as long as it is not the current year, because we are only 45 weeks into the year.

When I try:

(

IF [Is Current Year] THEN

DATEPART('week', NOW())

ELSE 52

END

)

I get errors about mixing aggregates and non aggregates. I need to be able to use DATEPART('week', NOW()) (currently = 45) as a constant for the divisor.

I have tried wrapping it in INT() with no luck (same error message). Wrapping the date part in SUM() does as well. Any ideas?

• ###### 1. Re: How to treat this as a constant? Or something... Mixing agg/non-agg problem

When you sum up the whole statement it should work:

I tried this with the coffee data and it seemed to work:

SUM([Budget Margin]) /

SUM(

IF DATEPART('year',[Date]) == DATEPART('year',NOW()) THEN

DATEPART('week', NOW())

ELSE 52

END

)

So your calculation should be like:

SUM(

IF [Is Current Year] THEN

DATEPART('week', NOW())

ELSE 52

END

)

1 of 1 people found this helpful
• ###### 2. Re: How to treat this as a constant? Or something... Mixing agg/non-agg problem

Well, I tried that, but that does not fix it. The values are are fraction (about a tenth, to be exact) of what they should be.

So, it is doing some sort of sum still, even when evaluating to the constant 52.

BUT... I tried average instead and that DOES yield the expected result. So, the slightly modified calc is then:

AVG(

IF [Is Current Year] THEN

DATEPART('week', NOW())

ELSE 52

END

)

Again, Thank you!

• ###### 3. Re: How to treat this as a constant? Or something... Mixing agg/non-agg problem

Weird, it did work in my workbook, in this case it would not matter obviously. Otherwise you could sum the individual metrics inside of the if-statement or remove the [ad] sum and sum up the entire calculation.