
1. Re: calculate six month average for first six months of 2016
Michael Ye Feb 14, 2018 2:02 PM (in response to mark schukas)Mark,
Please see attachment (V10.2) and screen shot.
Build a calculated field: Avg of first 6 months and put in detail, then you can add reference line:
WINDOW_SUM(SUM(IF MONTH([Order Date])=1 THEN [Sales]
ELSEIF MONTH([Order Date])=2 THEN [Sales]
ELSEIF MONTH([Order Date])=3 THEN [Sales]
ELSEIF MONTH([Order Date])=4 THEN [Sales]
ELSEIF MONTH([Order Date])=5 THEN [Sales]
ELSEIF MONTH([Order Date])=6 THEN [Sales]
END))/6
The avg of 12 months is 61,162.
The avg of first 6 months is 42,940.
Hope it helps.
Michael Ye

avg6.twbx 1.1 MB


2. Re: calculate six month average for first six months of 2016
Justin Larson Feb 14, 2018 2:06 PM (in response to mark schukas)1 of 1 people found this helpfulThere are a number of ways to answer this question, dependent on nuance of how you want this figure to be used or shown on a report. You'll probably need to be more specific to get a reasonably useful answer, aided largely by a sample workbook that gets as close as you can, or shows where you ran into the error.
For example, when you say average sales, do you mean the average value for each row? Average monthly sales? Do you need to just show this figure on the canvas by itself? Do you need to compare this number to other measures?
Based on answers to each of these, you may get different approaches with varying combinations of layout, calculation, and filter configurations.

3. Re: calculate six month average for first six months of 2016
mark schukas Feb 14, 2018 3:57 PM (in response to mark schukas)thank you.
at the moment, I just need to create the 6 month average (Sales) and show on a Worksheet
(e.g., it's used as a comparison: this year we sold $100 for January last year's monthly average was $90  business rule is to compare to first 6 month's average for 2016)
thanks...

4. Re: calculate six month average for first six months of 2016
mark schukas Feb 14, 2018 4:16 PM (in response to Michael Ye)thank you.
seems to work OK for single Measure (Sales).
but if I need to use a Calc. Field (e.g., Ratio: Sales/Profit), I get the error: "can't mix aggregate...with an If statement"
?
thank you.

5. Re: calculate six month average for first six months of 2016
Justin Larson Feb 14, 2018 4:19 PM (in response to mark schukas)1 of 1 people found this helpfulSo, you can do a calculation to evaluate if the date is in first six months of 2016. If so, return sales, of not, null. Then wrap that in your aggregate. As in:
AVG(
IF YEAR([Order Date]) = 2016
AND MONTH([Order Date]) <= 6
THEN [Sales]
END
)

6. Re: calculate six month average for first six months of 2016
Michael Ye Feb 14, 2018 5:56 PM (in response to mark schukas)We can do just like the following:
When Sales in a month are bigger than the first 6 months Avg Sales, it is green, otherwise, red.
Thanks,
Michael Ye

avg6_2.twbx 1.1 MB


7. Re: calculate six month average for first six months of 2016
Michael Ye Feb 14, 2018 6:17 PM (in response to mark schukas)Mark,
Please remember Profit/Sales is a row level calculation. It's nonsense. Because when it moves to column or row, it will aggregate to SUM(profit/sales) or
AVG(profit/sales), etc, They are no significance. Only Sum(Profit)/Sum(Sales) represents the profit ratio. When it moves to the column or row, It never aggregate again, That is significant.
If you want to compare the profit ratio, you can modify the calculation:
WINDOW_SUM((IF ATTR(MONTH([Order Date]))=1 THEN SUM(Profit)/SUM([Sales])
ELSEIF ATTR(MONTH([Order Date]))=2 THEN SUM(Profit)/SUM([Sales])
ELSEIF ATTR(MONTH([Order Date]))=3 THEN SUM(Profit)/SUM([Sales])
ELSEIF ATTR(MONTH([Order Date]))=4 THEN SUM(Profit)/SUM([Sales])
ELSEIF ATTR(MONTH([Order Date]))=5 THEN SUM(Profit)/SUM([Sales])
ELSEIF ATTR(MONTH([Order Date]))=6 THEN SUM(Profit)/SUM([Sales])
END))/6
You need to add ATTR() to change nonaggregate calculation(such as MONTH([Order Date]) to aggregate calculation (such as SUM(Profit)/SUM([Sales]).
Here, the Avg Profit ratio of first 6 month: (0.1613+0.0795+0.2506+0.0256+0.1433+0.1552)/6=0.1359, so it is correct.
Hope it helps.
Michael Ye

avg6_3.twbx 1.2 MB


8. Re: calculate six month average for first six months of 2016
mark schukas Feb 15, 2018 7:50 AM (in response to mark schukas)thank you...
closer...
works as you show above (good
I would now like to generate the First six month AVG Profit and show as only on cell (.1359)
(no months on Column)
make sense?
thanks...