▼

Tuesday, January 21, 2020

FAQ Series- Cannot Mix Aggregate and Non-aggregate

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  - 


at

7 comments:

  1. anncutrellJanuary 24, 2020 at 3:26 PM

    Great resource for teaching. Thanks Jim!

    ReplyDelete
  2. www.jimdehner.blogspot.comJanuary 26, 2020 at 11:06 AM

    Thanks Ann - I'm doing a series on FAQ's from the Forum - stop back - the next is on Nulls

    ReplyDelete
  3. KrishnaMay 21, 2020 at 3:39 AM

    Thanks for sharing Jim. It really helpful.

    ReplyDelete
  4. Tim BeardJune 24, 2020 at 5:07 AM

    This comment has been removed by the author.

    ReplyDelete
  5. Tim BeardJune 24, 2020 at 6:15 AM

    Excellent post Jim - thank you!
    I understand the issue, but it still catches me. Your tips to identify what are aggregates or not were particularly useful.
    I recently voted on this idea to visually differentiate between aggregates and non-aggregates: https://community.tableau.com/ideas/10853

    ReplyDelete
  6. UnknownJune 25, 2020 at 3:23 AM

    Hi Jim,
    I wonder if you can help me understand and resolve a problem I am struggling with. I have about 10 years of data joined by date at the day level. For three different columns (say A, B and C) I have the a value that corresponds to the value on that date.

    I have a bar chart display that has the date grouped by year and I want to show the "total" value from each of A,B and C that corresponds with the last day of that year where I have a date. So - for 2011 it might be 30-Dec-11 as that is the last day I have a value in that year. I know this because I have created MAX([Date]) and filtered by that date.

    I simplistically thought I would be able to grab the corresponding value from A, B or C that exists in the column for the MAX(Date) at the year level, however, what I get is SUM([A] etc. ) which is the sum of all the values in A up until the last day of the year. A huge number!

    I've tried, LOOKUP with INDEX and LAST etc. in a calculated field using MAX([Date])but am blocked in grabbing this atomic value from the column as it requires an aggregated value (SUM([Total])). I can resolve this, pretty sure, by creating a new column in my data source that shows only the additions or subtractions in A, B or C and not their "current" value for that date. In this case I think the SUM([A]) will correspond, but I figure there is more likely something I am fundamentally missing to implement this with the data structured as is.

    Is my explanation clear? and if you have a moment, do you see something obvious you could point out to me?

    ReplyDelete
  7. www.jimdehner.blogspot.comJune 25, 2020 at 6:35 AM

    Hi
    Thanks great question - just reading the post it sounds like the data need to be pivoted - but without seeing the actual workbook it is a little difficult to provide a specific solution- Suggest you post your question on the Forum at https://community.tableau.com/community/forums and include your TWBX workbook -
    Thanks

    ReplyDelete

Note: Only a member of this blog may post a comment.