I'd like a good clear answer on this as well. You can often get the same results with both, but I know there are occasions where one is more appropriate than the other. In the past, I think there were performance differences as well, but I'm not sure if those still exist.
I know Total doesn't accept as many arguments inside the function, so I think WINDOW_SUM has to be used in cases where you want to use offsets, etc. to calculate.
OK, I think I finally got it. I guess the problem is that with SUM operation it is always the same result, that is why it was confusing for me.
TOTAL(SUM(X)) = WINDOW_SUM(X) = WINDOW_SUM(SUM(X))
but [Assuming same "Calculate Using"]
TOTAL(AVG(X)) = WINDOW_AVG(X) != WINDOW_AVG(AVG(X))
Is that the correct asumption?
P.S: I know it does not exist such a thing like WINDOW_FX(X) since X must be aggregated, but it is to
describe it the best I can.
P.S2: Was your answer addressed to the exlcusive case of summing?
Its not always the same result with SUM. With WINDOW_SUM, you can set start and end arguments (offsets like FIRST(), LAST(), etc), allowing you to specify a specific area within the window/partition to SUM. Total does not have this ability.
I do not really understand your assumption, so I cannot comment on that.
My assumption and at least in my test it seems so, is that TOTAL applies the aggregation to all underlying entries in the partition whereas WINDOW_FX uses the function on the already aggregated values.
That is why I believe TOTAL(SUM(X)) = WINDOW_SUM(X) while WINDOW is WINDOW_SUM(SUM(X))
I mean fro mtheoretical point of view, I understand than appart from that, WINDOW is more flexible, it allows to indicate rows for the window as you said, and also include coniditions inisde
1 of 1 people found this helpful
The purpose of TOTAL is to compute the given aggregate expression at the coarser granularity of your partitioning fields. By omitting the addressing fields and only performing GROUP BY on the partitioning fields, the TOTAL table calculation can perform aggregation that cannot properly be rolled up with a WINDOW_* calculation.
Consider the aggregation AVG. There is no meaningful calculation involving WINDOW_*(AVG(..)) that can represent the average value at the granularity of the partition. You wouldn't want to use WINDOW_SUM on a collection of averages computed along your addressing fields. And WINDOW_AVG doesn't make sense either since it's just an average of averages, and does not represent the overall average.
This becomes even easier to understand with non-additive aggregations like MEDIAN or COUNTD. The TOTAL table calculation will compute the overall MEDIAN or overall COUNTD for the given expression, which is impossible to evaluate if you first aggregate along addressing fields and then attempt to summarize.
I hope this helps,
5 of 5 people found this helpful
Nice description, Robert! I like the mention of MEDIAN and COUNTD, I'll have to work that into my own descriptions. A shorthand that Joe and I have come to use is:
- WINDOW_ functions are an aggregation of an aggregation, for example, WINDOW_SUM(SUM([Sales])) will sum all the SUM([Sales]) across the partition, while WINDOW_SUM(AVG([Sales])) will find the sum of all the AVG([Sales]) across the partition.
- TOTAL() is an aggregate at a higher level (i.e. the coarser level of granularity). So TOTAL(SUM([Sales])) will sum all the sales across the partition, which creates the same result as the default WINDOW_SUM(SUM([Sales])) but via a different route. TOTAL(AVG([Sales])) will find the average of sales across the partition, which is quite a bit different from the WINDOW_SUM(AVG([Sales])).
Thanks Robert and Jonathan for your clear explanation about the difference between TOTAL and WINDOW_* calculations.
Duuude, you're using a visual!! Who woulda thunk of that?
Thank you both! Crystal clear! Its going to be difficult pick the correct answer since both are great! I go for the first
I think is exactly what I thought!
Would be right say that TOTAL is nearly an alias for WINDOW_FX(X) instead of the available WINDOW_FX(FX(x)) ?
Hi John, I'm glad they helped!
I think your latter point is a potentially dangerous oversimplification. In Tableau terms, X is a non-aggregated field. There are two ways to have one in a view - via putting X in the view as a dimension, in which case it becomes part of the GROUP BY in the query that determines the value of every other measure in the view, or by having SUM(X), MIN(X), etc. aka FX(X) in a view that has Analysis->Aggregate Measures turned off, in which case X does not increase the level of detail for other calculations but does return every row in the data.
Both TOTAL() and the WINDOW_ functions require aggregate measures, so a variation on your statement would be to say that TOTAL(FX(X)) is returning FX(X) at whatever level of aggregation you want to set up via the addressing and portioning, whereas WINDOW_FX(FX(X)) is the WINDOW_FX() aggregation of the FX() aggregation.
Thank you Jonathan. I see your point!
Could I reformulate it as TOTAL(FX(X))) = TOTAL_FX(X) if TOTAL_FX existed. Would be that true?
(I know I am picky, just trying to see if I omit anything evident to draw my mental schema)
"TOTAL(FX(X)) = TOTAL_FX(X) if TOTAL_FX existed" is a valid statement as far as I can think, and if it works for you then great!
I don't mind your pickiness at all, it's actually helping me to get more particular (picky) myself! In the back of my mind there's been some resistance to the attempted simplification of TOTAL, and I think I've got a better handle on it, this relates to what I was talking about with dimensions. Understanding the dimensions in the view (i.e. what's a dimension and what isn't), how the dimensions create cells, the cardinality of the dimensions, relative sparseness/completeness of the data, and in the case of blending how all of the above relate to each other are critical to making the results of Tableau predictable.
FX(X) is a measure that will be computed for each intersection of dimensional values in the view. TOTAL(FX(X)) lets you work at almost any level (partition) you want, from computing the FX(X) at each intersection all the way up to just once for the entire view.
Personally, the "TOTAL(FX(X)) = TOTAL_FX(X) if TOTAL_FX existed" formulation makes things more complicated for me because it's making a comparison to an imaginary function, and I think the more important aspect of TOTAL is about its power to change how the data is partitioned for the measure.
Also, if you are looking for TOTAL with an offset, here are a couple of Ideas to vote up:
One more thing that just occurred to me after I pressed submit. (I'm replying to my own reply because Jive doesn't seem to generate notifications on edits anymore).
With the WINDOW_ functions, Tableau has defined the FX of the WINDOW_FX - the aggregates of aggregates - that are supported (SUM, MIN, MAX, COUNT, STDEV, MEDIAN, VAR). If we want to go beyond that, we have to do build our own calcs. With TOTAL(), we can create most any aggregate calc we want (except for a calculation that uses fields from multiple data sources) and then aggregate that at whatever level. So the FX in the hypothetical TOTAL_FX(X) in actuality can be something like TOTAL_(SUM(numerator)/SUM(denominator)), etc.