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

    Aggregate/Non-aggregate expressions

    Kate Leander

      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.