
1. Re: Moving average of filtered measure(s)
Carl Slifer Jan 11, 2016 5:03 PM (in response to Lorenzo Carlisle)Howdy Lorenzo,
I do not see that this should be an issue with aggregating and nonaggregating. This is because we should have two separate measures that are being triggered for the parameter. One that is either Clicks or Opens and another that is the moving average of these.
TRY
[Open or Click Field]
CASE [Parameter]
WHEN 'Open' THEN [Opens]
WHEN 'Clicks' THEN [Clicks]
END
[Moving Average of Open or Click Field]
WINDOW_AVG(SUM(
CASE [Parameter]
WHEN 'Open' THEN [Opens]
WHEN 'Clicks' THEN [Clicks]
END
),3,0)
If there is some issue that I cannot see please let me know At worst if say clicks are not aggregated and Opens are you can just wrap inside the statement
[Open or Click Field]
CASE [Parameter]
WHEN 'Open' THEN [Opens]
WHEN 'Clicks' THEN SUM( [Clicks])
END
Cheers!

2. Re: Moving average of filtered measure(s)
Lorenzo Carlisle Jan 11, 2016 5:29 PM (in response to Carl Slifer)Hi Carl,
Thanks very much for the response. I will have to use that window_avg function!
However, the aggregating/nonaggregating problem is actually that, besides whole number measures like opens and clicks, other measures are calculated rates. For example, two of the measures  "opens" and "sent"  are actually used to calculated a third measure, "Open Rate", via division.
It is not possible to wrap something like agg([Open Rate]) inside of the case statement in the same way that it's possible to do sum([Clicks]).
Does this clarify the issue?

3. Re: Moving average of filtered measure(s)
Carl Slifer Jan 11, 2016 5:47 PM (in response to Lorenzo Carlisle)1 of 1 people found this helpfulHi Lorenzo,
The window_avg function is actually not that fancy. You can cheat too! When you create a table calculation with the 'quick table calc' option you can drag the measure off and onto the measures pane and then you can see how it is actually being calculated 'behind the scenes'. Its just how Tableau creates the moving average calculation.
Okay, opens and clicks calculated field works because they are both whole number measures. But how are you calculating rates? It would guess something as simple as the number opened divided by the number sent?
Why can we not use
[This is the Rates]
CASE [Parameter]
WHEN 'Open' THEN SUM(Open) / SUM(Rate) < Here we could actually just use the already calculated OPEN RATE field
WHEN 'Click' THEN SUM(Clicks) / SUM(XYZ) < Here we can use the already made CLICK RATE Field
END
I'm shooting in the dark from my hip here because I am not privy to your data structure. I think you're pretty close with asking about the agg([open rate]) lets chat about that. If we have a clear workbook and pull a measure onto a shelf somewhere by default Tableau wants to do a sum aggregation. It is being aggregated! But the type of aggregation is a sum, its adding everything up, no brainer right? Well we can do that with the AVG as well as the MIN, MAX. it's aggregating to a certain level already. And it aggregates to this degree by reading every row in the partition. IF we do sum by itself we add everything but if we do sum with a date field on the orders shelf it sums everything within that date. The other aggregations work the exact same.
So the AGG() that you see when you put something onto the field is pretty simple to understand. It's saying its preaggregated and it's preaggregated based on what the underlying calculation wanted. So if you took SUM(X) / AVG(Y)... you know for whatever reason it would be preaggregated. Because it's not looking at two things in the same row but rather two columns within the same array in a partition. Its a bit tricky to understand without pointing and gesticulating at a white board but as you play with them more I think understanding WHY and WHAT is happening is more important than the I need to do X, right now. Then in the future I can quit posting here, retire on a tropical island and you get to train the next generation of padewans.
Cheers

4. Re: Moving average of filtered measure(s)
Lorenzo Carlisle Jan 12, 2016 10:49 AM (in response to Carl Slifer)Carl,
Thank you for the information on how Tableau aggregates data. It reinforced my current understanding.
I think what you're saying in the bit about calculating rates is that I do not actually need to use the aggregate values (i.e. "open rate", "click rate") and can simply divide the nonaggregate values to calculate them, instead. I had not thought of that, but that will actually work! Not sure if that is in fact what you intended, but you talked me through it, regardless.
Many thanks!
.

5. Re: Moving average of filtered measure(s)
Carl Slifer Jan 12, 2016 11:10 AM (in response to Lorenzo Carlisle)Glad to head it Lorenzo. I'd rather give people to the tools to succeed than build the house for them each time.
Enjoy!