8 Replies Latest reply on Dec 12, 2016 7:23 AM by Tom W

    Calculations in Tableau

    Susan Kurian

      Hello,

       

      I am trying to find out the total number of students based on a type field. I only want to display total numbers for a particular week. I have created two functions calc_DateDiff and Calc_CurrWeek. The DateDiff field is used to calculate difference between field [Date] and Today(). Then based on this difference, get the value in the Week field. But my calculated value is showing null. I have attached the workbook.

       

      Please Advise.

       

      Thanks,

      Susan

        • 1. Re: Calculations in Tableau
          Tom W

          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

            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

              Beat me to it!

              • 4. Re: Calculations in Tableau
                Tom W

                Good stuff Keshia

                • 5. Re: Calculations in Tableau
                  Susan Kurian

                  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

                    Speaking 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])}

                    1 of 1 people found this helpful
                    • 7. Re: Calculations in Tableau
                      Susan Kurian

                      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

                        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!