In our client project this week, the dashboards we were looking at had some calculations containing the TOTAL( ) function. It seems pretty self-explanatory, but it was being used in a place where I would have used the WINDOW_SUM( ) function.
They are both Table Calculation functions. They both sum up values that are in your view.
This left me wondering, what’s the difference? In short:
- TOTAL( ) will always calculate the sum of all values in your window
- WINDOW_SUM( ) gives you more control over which values to include in your sum.
Both calculations will do the same thing if you do not set any values for where the WINDOW_SUM( ) function should start and end, or if you set it to start and end at the first and last values in the window, respectively. In other words, all the following calculations do the same thing.
Notice, the syntax for the WINDOW_SUM( ) function allows you to select a start and end point anywhere between the first and last value in the window. Here are some examples of what you can do with that:
1. Add only the current value (denoted by 0) and the next one in the window (denoted by the number 1). Of course you can expand this to include the next 2 or 3 or any number of values.
2. Add only the previous value (denoted by -1) with the current value (denoted by 0). Again, you can expand this to include the previous 2 or 3 or any number of values.
3. Add all values from the second value (denoted by FIRST( ) +1) to the second-to-last value (denoted by LAST( ) -1). You can edit this range by adding any number to the FIRST( ) function and subtracting from the LAST( ) function.
4. Add all values from the first value in the window to the number you are currently on. This is effectively a running total. But once again you have more control with this WINDOW_SUM( ) function because you could choose to exclude the first ‘X’ values from your running total by adding numbers to the FIRST( ) function, like in example 3.
5. Add all values from the current number to the last one in the window. This is effectively a reverse running total. Again, the WINDOW_SUM( ) function gives you more control since you can choose to exclude the last ‘X’ values from your sum by subtracting numbers from the LAST( ) function, like in example 3.
These are just some ideas. There are many more things you can do by combining any of the 5 examples mentioned above and I’m sure there are many things I am yet to learn. Some of these may rarely be needed, but there may be very specific examples where they come in handy and could save you some time.