6 Replies Latest reply on Mar 10, 2016 10:40 AM by satish.parvathaneni

    Counting with Fixed LODs

    Brit Cava

      Hi Tableau folks!

       

      I'm trying to work through the following use case but am having trouble getting to what I need to have. I'm sure this is probably very obvious and maybe my brain is over thinking this!

       

      Here's a pretty straightforward example of what I have and what I want:

       

      Screen Shot 2016-03-09 at 3.27.28 PM.png

       

      I think the Fixed LOD worked great for me to get the latest status by ID but when I want the count by latest status, it computes across all the IDs and gets the latest status for all IDs.

       

      Any thoughts?

       

      Pooja Gandhi - any thoughts?

        • 1. Re: Counting with Fixed LODs
          pooja.gandhi

          Hi Brit,

           

          Thanks for the ping, I do not have access to Tableau right now but I think this should be:

           

          {FIXED [ID]: MAX(IF [DATE] = { FIXED [ID]: MAX(Date} } then [Status] end) }

           

          Then place this calc on rows and CNTD([ID]) on text on the marks card.

           

          I may be totally wrong because I can't test this right now, so let me know if I was totally off

           

          Pooja.

          2 of 2 people found this helpful
          • 2. Re: Counting with Fixed LODs
            Brit Cava

            Yes - just a minor syntax error

            With my original LOD if I do distinct on ID it works as well - I'll have to further test.

             

            Thanks for your help!

            • 3. Re: Counting with Fixed LODs
              pooja.gandhi

              Great, I think with your original formula, it is giving you MAX(Status) and because MAX on a string is alphabetical it throws 'Middle' as the result because 'M' is the max of the series and count of ID on text will hence give a 7.

               

              So if you are using your formula, you will need to wrap the entire formula under {FIXED ID: [Your Formula]} to get the right results.

               

              Hope that helps clarify this a little more

              • 4. Re: Counting with Fixed LODs
                spenser.troiano

                I had nearly an identical question and this solution was perfect. Thanks!! I admit I'm still learning quite a bit when it comes to programming function syntax, and Tableau's in particular, so I don't understand why it works. Is there a layman's terms explanation on what is going on?

                • 5. Re: Counting with Fixed LODs
                  pooja.gandhi

                  Hi Spenser!

                   

                  With LODs or any other nested formulas, my motto is:

                   

                  "When in doubt, break it apart from inside out"

                   

                  So take the nested expression I suggested above:

                   

                  {FIXED [ID]: MAX(IF [DATE] = { FIXED [ID]: MAX(Date} } then [Status] end) }

                   

                  and break it apart looking at the innermost loop first:

                   

                  1st: { FIXED [ID]: MAX(Date) } - This gives you the max date at the level of ID.

                  2nd: IF [Date] = [1st] then [Status] end - Basically saying that if date equals the resulted date in the 1st expression, then give me status:

                   

                   

                  3rd: {FIXED [ID] : max([2nd]) } - Notice there are nulls in the result of the 2nd expression, that is because the date did not equal the resulted date of the 1st expression. So what the 3rd expression tells Tableau is give me the max of the results of the 2nd expression and hence fills all rows with the result of the non-null values from the 2nd expression:

                   

                   

                  So that when you remove all the fields out of the view, the FIXED expressions would still retain the values of max dates. The resulting view then would be:

                   

                   

                  And counts of ID's for each max status would be:

                   

                   

                  So basically all I did was merged all 3 expressions into 1 in my initial response to Brit.

                   

                  There are some great resources out there on this topic. Here are a few:

                   

                  Top 15 LOD Expressions | Tableau Software

                  Understanding Level of Detail (LOD) Expressions | Tableau Software 

                  http://www.tableau.com/sites/default/files/whitepaper_lod_eng_high.pdf

                   

                  Hope this helps,

                  Pooja.

                   

                   

                   

                   

                  1 of 1 people found this helpful
                  • 6. Re: Counting with Fixed LODs
                    satish.parvathaneni

                    Wow ...Nice Explanation . Thank you !!