5 Replies Latest reply on Apr 11, 2018 9:41 AM by Jim Van Sistine

# Aggregate/Non-aggregate expressions

Wondering if anyone can help me with this calculation... I need to figure out the number of orders we have had YTD this year vs. YTD in 2017 and 2016. I created a calculated field to count the number of orders, but I cannot use this field in conjunction with an if/then expression because it's aggregated. I would like to do something like the following, which I successfully used to get YTD Sales for the past 3 years:

IF

(YEAR([Order Date]) = YEAR(TODAY())-1

AND

(MONTH([Order Date]) < MONTH(TODAY()) OR (MONTH([Order Date]) == MONTH(TODAY())

AND

DAY([Order Date]) <= DAY(TODAY())))

)

THEN ([Number of Orders])

ELSEIF (YEAR([Order Date]) = YEAR(TODAY())-2

AND

(MONTH([Order Date]) < MONTH(TODAY()) OR (MONTH([Order Date]) == MONTH(TODAY())

AND

DAY([Order Date]) <= DAY(TODAY())))

)

THEN ([Number of Orders])

ELSEIF YEAR([Order Date]) = YEAR(TODAY()) then ([Number of Orders]) END

But, I keep getting the cannot mix aggregated/non-aggregated error. I think the solution might be an LOD, but I'm unsure how to set it up. Apologies, but I cannot include a packaged workbook due to confidentiality issues.