
1. Re: Calculations in Tableau
Tom W Dec 8, 2016 9:44 AM (in response to Susan Kurian)Take a look at the data in your file;
Your Calc_CurrWeek field is saying "if the datediff is less than or equal to 3", but the date diff on all those dates is huge, they definitely don't fit within the bounds of the less than or equal to three. That's why everything is returning as null.
I'm not entirely sure what you're trying to achieve here, so help me with some further details relative to this data table and we'll be able to figure it out!

2. Re: Calculations in Tableau
Keshia Rose Dec 8, 2016 9:46 AM (in response to Susan Kurian)Hi Susan,
Based on your calculation it looks like there are no values of [Calc_DateDiff] that are less than or equal to 3 days. The smallest value I see is 17 days for November 21, 2016. Therefore, since your calculation does not give an alternative (ELSE) for when the criteria (days <= 3) isn't met, you are getting null values.
Let me know what results were you hoping to see for the [Calc_CurrWeek] calculation and we can probably figure out what changes need to be made to get it to work correctly.
Take care,
Keshia

3. Re: Calculations in Tableau
Keshia Rose Dec 8, 2016 9:47 AM (in response to Tom W)Beat me to it!

4. Re: Calculations in Tableau
Tom W Dec 8, 2016 9:48 AM (in response to Keshia Rose)Good stuff Keshia

5. Re: Calculations in Tableau
Susan Kurian Dec 8, 2016 9:57 AM (in response to Tom W)Hi Tom,
What I am trying to do is compare today's date to the latest date in the file and if it is below for this test say 17 days then I would like to get the corresponding week value. Based on this corresponding week value, I would like to add up the total numbers for a school for a particular term. I am trying to avoid using week as a filter, would like it be automatically populated. Attached is a screen shot of what I would like to do without using week as a filter. Hope this makes more sense.
Thanks,
Susan

6. Re: Calculations in Tableau
Tom W Dec 8, 2016 10:27 AM (in response to Susan Kurian)1 of 1 people found this helpfulSpeaking relative to your data, the most recent record is from 11/21/2016 and it has a week number of 4. You'd effectively like to filter on week number 4 as that's the corresponding week on the highest date in your file?
If so, here's how I did it. Note this can be done in one calc but I wanted to break it up so you could see how it works;
1. Create a field called 'Max Date for Week' as: {FIXED [Week]:MAX([Date])}
This is using a Level of Detail calculation to determine the highest date for the given week. I.e.
2. Create a field called Max File Date as:
{FIXED:MAX([Date])}
Again, another level of detail calculation. This level of detail calculation will run across the entire dataset. It's not partitioned by Week like the LOD calc in step 1.
So you'll see our two fields at work here, now we just have to create a field which logically compares them
3. Create a field called Is Max Date as:
[Max File Date]=[Max Date For Week]
This will return True or False based on the condition above. So when it all comes together you see:
Go back to your chart now and drop Is Max Date onto the filters shelf and select True.
If you want to combine that all into one calculation, create it as {FIXED:MAX([Date])} = {FIXED [Week]:MAX([Date])}

7. Re: Calculations in Tableau
Susan Kurian Dec 9, 2016 8:34 AM (in response to Tom W)Hello Tom,
Thank you so much. The solution you provided did the trick. Also a great Thank You for explaining in detail. I have not yet used the FIXED function. Going to be looking up on those links to understand it further.
Susan

8. Re: Calculations in Tableau
Tom W Dec 12, 2016 7:23 AM (in response to Susan Kurian)Hi Susan,
I'm glad I could help. Please be sure to mark the answer as correct going forward to close out the thread.
Thanks!