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.

• ###### 1. Re: Aggregate/Non-aggregate expressions

Can you post screenshot for Error?

• ###### 2. Re: Aggregate/Non-aggregate expressions

Good morning

Is Number of orders a calculated field that is aggregated? - if so every date will need to be aggregated also with attr(), max(), min() or whatever is appropriate in the viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Aggregate/Non-aggregate expressions

This is the screenshot - I know the problem is that Number of Orders is already aggregated but I just don't know how to correct the calculation to account for that.

• ###### 4. Re: Aggregate/Non-aggregate expressions

Kate,

Write ATTR(...) Everywhere

• ###### 5. Re: Aggregate/Non-aggregate expressions

Could you separate out the YTD logic and use it as a filter instead?

I did the attached using SuperStore, and then just built the viz with Sum(Sales)