Thank you Weston...I knew this capability had to exist...you just have to ask the RU-vid search engines the right question. Great stuff and well explained.
My first thought would be to make the date filter a context filter. If that doesn't work, you may need to add the date field into the FIXED function. (I responded before reviewing the video, so I am not sure which is the correct solution. If neither work, let me know and I'll take a closer look.)
Hi Weston, this is really helpful and this is the exact problem I'm facing. How would I go about doing this if both of my fields are calculated counts? I've got the total number of projects and want to know a user's contribution to the total number of projects within a specific date. your formula does not work because I cannot mix aggregate and non-aggregate fields. I've been stuck with this problem for the past two days. I would really appreciate your help. This is what my formula looks like: SUM([Total_Names])/MAX({SUM([Project_Count])}) each field being a calc. field that counts names and projects
I would first play with the Year over Year change (table calculation). If you don't want to see the previous year's value, you can hide it. To make it dynamic is more involved. You may have to create a separate calculated field to calculate the previous year's value. Then create another field to calculate the percent change. Let me know how that works, or if you have sample data maybe I can work something up for you. (Sorry it took so long to respond.)
hi Weston thanks for the wonderful video! i m using tableau 2020 and calculations doesnt seem to hv SUM how do i edit calc to achieve the same result, please?
SUM is a standard function in Tableau regardless of the version, so I must not understand your comment. Where is SUM not showing up? When I use it in the video inside the { }, I am omitted the LOD expression FIXED and the ":". You can do that if you don't have to filter on any other dimension. If you are looking for {SUM}, you won't find it in any of the help documentation, because it is a shortened version of another function.
You can certainly add more filters, however because this method using a level of detail function, the other filters will need to be added to the function. If I were to do this again, I would use {FIXED :sum(Sales)}. If you want additional filters, place the field names between FIXED and the ":". For example {FIXED [State],[Category]:sum(Sales)}. You'll still need to wrap the whole formula in the MAX function so Tableau recognizes it as an aggregate function. Good luck.
