11 Replies Latest reply on Aug 23, 2017 5:09 PM by Braydon Davis

# Grab Total Sales for Current Year & Growth %

Hoping to receive some help to solve this challenge

Dimension: Month_Year (Format: DATE ,Value Example: 2017-05-01, 2017-06-01, etc.)

Measure: Sales

Objective 1: Grab total sales for current year. Example: sum all months to give me the total sales for the current year.

Calc1: Window_Max(Year(Month_Year) THEN TOTAL(Sales) = Current Year Sales

Objective 2: Grab total sales for current year and previous year. Calculate year over year growth.

Calc 2: Window_Max(Year(Month_Year)-1 THEN TOTAL(Sales) = Previous Year Sales

Calc 3: Calc1-Calc2/Calc2 = Growth %

Output example:

2017 Current Year Sales: \$300,000

Year over Year Growth %: 20%

I've tried window_max and various other functions but could not get to work. Any help is appreciated.

• ###### 1. Re: Grab Total Sales for Current Year & Growth %

Hi,

I used these two Formula on the Superstore dataset.

Current Year:

Previous Year:

Change:

• ###### 2. Re: Grab Total Sales for Current Year & Growth %

This is great however I get the non-aggregate with an aggregate expression issue due to the fact that "Sales" is aggregated. Is there a solution to this?

• ###### 3. Re: Grab Total Sales for Current Year & Growth %

Just use the Way I wrote 2 Calculations for Current and Previous. You will be fine.

• ###### 4. Re: Grab Total Sales for Current Year & Growth %

Example of formula plus error for Current:

• ###### 5. Re: Grab Total Sales for Current Year & Growth %

What is formula for Billings

• ###### 6. Re: Grab Total Sales for Current Year & Growth %

"Billings" is a simple sum of "Sales" so for that I can simply sum the "Sales" field.

For other columns such as  "Net Adds", the formula would consist of: IF ISNULL([Churn]) THEN sum([New Orders]) ELSE (sum([New Orders]))-[Churn] END

OR

Column "AOV" consists of: [Billings]/sum([Number Of Orders])

• ###### 7. Re: Grab Total Sales for Current Year & Growth %

Can't do much without your workbook. If you can post what you are doing. May be an Exact mock Example.

• ###### 8. Re: Grab Total Sales for Current Year & Growth %

Remove Sum from your formula and Bring it in front as:

Just a Try

SUM({FIXED: (IF.......)}

• ###### 9. Re: Grab Total Sales for Current Year & Growth %

I tried to take out the aggregated measures and just getting wrapped around the flag pole at this point.

For Net Adds: SUM({FIXED : (IF (Year([Month Year])) = {MAX(Year([Month Year]))} THEN (IF ISNULL([subs_churn]+[refund_orders]+[chargeback_orders]) then [New Orders] else [New Orders]-([subs_churn]+[refund_orders]+[chargeback_orders]) end) END}

• ###### 10. Re: Grab Total Sales for Current Year & Growth %

Then What happened?

• ###### 11. Re: Grab Total Sales for Current Year & Growth %

The below formulas gives me: Result of a level of detail expression must be an aggregate.

SUM({FIXED : (IF (Year([Month Year])) = {MAX(Year([Month Year]))} THEN (IF ISNULL([subs_churn]+[refund_orders]+[chargeback_orders]) then [New Orders] else [New Orders]-([subs_churn]+[refund_orders]+[chargeback_orders]) end) END)})