14 Replies Latest reply on Dec 5, 2018 12:24 PM by Ken Flerlage

# Sum of values on Max Date

Hi Experts , above is the sample data..

From above data , days = end-date-start_date filtered for March month. We can see id's 7 & 9 , they appeared twice , they  have same start-date but difference in end_date. When i do SUM(days) for everything it is giving 59 days .. But my requirement is , I needed only days for Max(end_date) if we do SUM(days) , ie., now total for SUM(days) would be 55 days ,as i dont want to consider days for Min(end_date) for id's 7 & 9.

in detail , for id 7 , if we do end-date-start_date we get number of days as 2 & 3. I want to consider only highest value ie., 3 from id 7 when i do Sum(days).

• ###### 1. Re: Sum of values on Max Date

What's the end goal? What do you actually want to show in Tableau? That will make a big difference in how we approach this. For example, if you just wanted to see this in a table format, you could do this:

The problem here is that grand totals won't work properly. So, we could use an LOD instead of just aggregating Days.

Days LOD

// Max days for each ID.

{FIXED [ID]: MAX([Days])}

Using an LOD will ensure that the grand total will not double count, so you'll get the correct grand total.

But those additional records will still be there, so you may want to just filter those out altogether. To do that, we can create a calculated field like this:

Include/Exclude

// Filter out records that aren't the max days for a given ID.

IF [Days]=[Days LOD] THEN

"Include"

ELSE

"Exclude"

END

Then you can add this as a filter and only include the records with "Include". Once you do this, you'll be able to use either SUM(Days) or SUM(Days LOD) since we're only including the records with the max number of days.

See attached workbook.

• ###### 2. Re: Sum of values on Max Date

Thank you Ken for your quick response , i will validate on my end and will get back to you.

My goal is to avoid min(days) for that particular id and want to use max(days) number while doing SUM(days).  I wanted to show total number of days in that particular month for specific id.

Thank you once again.

• ###### 3. Re: Sum of values on Max Date

I'd probably go with the last option then and just exclude the records altogether.

If this works, then please be so kind as to mark my response as the "correct answer" so we can close this thread and others can easily find the answer. Thanks!

• ###### 4. Re: Sum of values on Max Date

I am unable to open the workbook attached. And also when i drag days lod it is not giving the days difference , it is taking value as 1 for all and when we do SUM(days lod) its giving value as 10. Filter also not working for me.

• ###### 5. Re: Sum of values on Max Date

I've attached with a previous version.

I made up a data set since you did not provide one, so mine may work differently than yours. Can you provide your actual packaged workbook?

• ###### 6. Re: Sum of values on Max Date

I tried to replicate these work around with my Original data , i cant share it sorry , its very confidential.

• ###### 7. Re: Sum of values on Max Date

Are you calculating Days in Tableau or is it a field in your data? If calculated, how are you calculating it? Can you share your calc for Days and Days LOD?

• ###### 8. Re: Sum of values on Max Date

'days' is already existed field in data , i did not calculate it. For 'days lod' formula i used is '{fixed[unique_id]:max(days)}

• ###### 9. Re: Sum of values on Max Date

I'm not sure how else to help without seeing a workbook. Any chance you could make up some data to share? Or could you anonymzing your data using this? Anonymize your Tableau Package Data for Sharing

• ###### 10. Re: Sum of values on Max Date

Really appreciate your time and efforts Ken , Thank you .. i will find other possibilities to give you clear picture of the data. Thank you once again.

• ###### 11. Re: Sum of values on Max Date

Create this Calc field:

IF [END_DATE] = {FIXED [ID] : MAX(END_DATE) } THEN True

ELSE FALSE

END

and place it to Filters. Then select only True in this filter.

What it does is it keeps only MAX dates

• ###### 12. Re: Sum of values on Max Date

Yes, I made that recommendation earlier.

• ###### 13. Re: Sum of values on Max Date

thank you for your response Vladimir . Unfortunately this work around is also not working.

• ###### 14. Re: Sum of values on Max Date

if i use the calc field provided by Vladimir , its considering nulls for id's 5,6,8,10 because their end date falls in next month. and it is considering remaining and doing sum