2 Replies Latest reply on Oct 9, 2018 7:52 AM by kristiina.truuverk

# Using IF condition for date with aggregated function

Hi,

I am trying to find a way how I could show the value for certain date (week) only. The value itself is an aggregated function. But when I try to combine the IF function it gives me an error of "cannot mix aggregate and non aggregate".

The function itself is:

IF [date]<=[WeekSelect]

and DATEDIFF('week',[date],[WeekSelect])<=1

and DATEDIFF('week',[date],[WeekSelect])>0

then

[AVG income user] END

Where [WeekSelect] is a parameter

and [AVG income user] = sum(income)/countd(user)

Point is to see the calculation value for the selected week. Why I use the calculated field is the be able later to calculate the variance between two different date periods.

Any help on how to find a solution for this is much appreciated.

• ###### 1. Re: Using IF condition for date with aggregated function

Kristina,

The function can be rewritten as

Average income user =

SUM(IF [date]<=[WeekSelect]

and DATEDIFF('week',[date],[WeekSelect])<=1

and DATEDIFF('week',[date],[WeekSelect])>0

then

[Income] END)/COUNTD(

IF [date]<=[WeekSelect]

and DATEDIFF('week',[date],[WeekSelect])<=1

and DATEDIFF('week',[date],[WeekSelect])>0

then

[user] END

)

1 of 1 people found this helpful
• ###### 2. Re: Using IF condition for date with aggregated function

Thank you!