11 Replies Latest reply on Oct 28, 2016 1:56 PM by Dereck Melo

    LOD Expression and ATTR

    Dereck Melo

      Hi Everyone,

      I cannot figure how to get a calculation to work as I start removing fields from the tables.

       

      What you will see.

       

      Image 2 shows a view that is working for me.

      Image 1 shows a view in which I remove one field and the calculation is only work for some of the data..

      Images 3 and 4 are the calculations I have going.

       

      I know this is rough but I'm a tight schedule for this and I don't have a moment to create a workbook that I can share the data. I understand if you cannot help but I thought I would ask

       

      thank you so much

      Dereck

        • 1. Re: LOD Expression and ATTR
          Stephen Rizzo

          It's hard to help without more information. With that said, my guess is that your issue is coming from the ATTR([BOMS-Rounds of Samples Allowed]). ATTR returns the unique value of the argument for all aggregated rows if one exists, otherwise returns *, which would make any calculation depending on that value return null. I would suggest rethinking exactly what you are trying to do with that ATTR function and replace it with something else.

           

          As for what to replace ATTR with, it really depends on how you want Tableau to handle the case where several TD codes have different values for [BOMS-Rounds of Samples Allowed]...

          • 2. Re: LOD Expression and ATTR
            Dereck Melo

            I think it's my rookie nature of not understanding what ATTR is actually doing. It was the only way I could get AGGREGATE and NON-AGGREGATE calculations to work

            • 3. Re: LOD Expression and ATTR
              Stephen Rizzo

              For what it's worth, when I started learning Tableau I did exactly the same thing.

               

              What exactly do you want [BOMS-TARGET-TEAM ROUNDS] to be? Specifically, can you explain how you would like Tableau to calculate the correct value of that field for one sample row?

              • 4. Re: LOD Expression and ATTR
                Simon Runc

                hi Dereck,

                 

                Stephen's explanation of what ATTR is...is spot on. However I think your question really relates to why you "can't mix aggregate and non-aggregate" calculations (which is the error when you tried your formula without the ATTR). The explanation for this is all about aggregation and Viz Level of Detail (vizLoD). I've written a brief quora answer on the different calculation types in tableau, which will hopefully make sense....

                 

                Answer - Quora

                 

                TL;DR - There are 4 calculation types which fall into 2 (general) types....On-canvas (types where the level of Viz detail, what levels are in your Viz, affects the calculation result) and Off-Canvas (calculation results are independent of what's on canvas). Row Level calcs (so no aggregation) are like your [BOMS-Rounds of Samples Allowed] are off-canvas, and COUNTD([....]) is an Aggregate...so you can't mix the two. wrapping the row level calc in a ATTR makes it an aggregate calculation, but would need the level you want it run at, in the Viz, to run the way you want.

                 

                Let me know how you get on, and if it makes more sense...I'll be happy to answer any questions.

                • 5. Re: LOD Expression and ATTR
                  Dereck Melo

                  Hi Stephen,

                  Thanks for the reply on this. So for [BOMS-TARGET-TEAM ROUNDS].. I simply want it to COUNTD([Prod Offer Master ID]) multipy that count by 3 and then add the values from [BOMS-Rounds of Samples Allowed].

                   

                  That would be my TARGET.  An example is on IMAGE 2 (starting at the top) the 4, 3, 5, 23 are the expected numbers.

                   

                  If I remove TD Code like in IMAGE 1 I just loose the data.

                  • 6. Re: LOD Expression and ATTR
                    Stephen Rizzo

                    OK, so let's take the example of the development team at the top of screenshot 2 with TD CODE D and F. For each TD CODE (row), what is the value for [BOMS - Rounds of Samples Allowed]? Then, when you remove TD CODE from the table, what should the new value of [BOMS - Rounds of Samples Allowed] for that same development team be?

                     

                    Sorry for asking all these questions, but understanding how you want to aggregate that field will help us figure out how the calculation needs to be changed.

                    • 7. Re: LOD Expression and ATTR
                      Dereck Melo

                      You know.. I am sorry for having to answer questions like I am I am working on a test file just to try to make it easier but to answer your question.

                      The value for D= 1 and for F=0

                       

                      Then when you remove TD CODE.. the value for the TEAM would be 7 (just the two added together)

                      • 8. Re: LOD Expression and ATTR
                        Stephen Rizzo

                        OK, then if I understand you correctly, [BOMS-TARGET-TEAM ROUNDS] for a particular development team should be the sum over all TD codes in that team of the number of distinct [Prod Offer Master ID] values for that TD code times 3 plus the (unique) value of [BOMS - Rounds of Samples Allowed] for that TD code.

                         

                        If that is the case, try this formula

                         

                        SUM({INCLUDE [TD CODE] : COUNTD([Prod Offer Master ID])*3 + ATTR([BOMS - Rounds of Samples Allowed])})

                         

                        Does that formula work / give you what you would expect?

                        • 9. Re: LOD Expression and ATTR
                          Dereck Melo

                          Okay.. So it's giving me an error...

                          Screen Shot 2016-10-28 at 1.31.24 PM.png

                          I have however added an attachment.

                          You will see 4 sheets - L1, L2, L1-People, L2-People.

                           

                          If you start with L2 that shows what I want.

                          It shows the Count, Target and Percentage for Each Team and Code

                           

                          Then if you go to L1 - you see how I loose the rollup

                           

                          Then L1 People - that one works as well

                           

                          Then L2 People - I loose Rollups again.

                           

                          You see what I mean?

                          • 10. Re: LOD Expression and ATTR
                            Stephen Rizzo

                            Easily fixed by replacing ATTR with MIN (which shouldn't matter since [BOMS - Rounds of Samples Allowed] should be unique for each TD code):

                             

                            The formula becomes:

                             

                            SUM({INCLUDE [Td Code]: COUNTD([Prod Offer Master Id]) * 3 + MIN([BOMS-Rounds of Samples Allowed])})

                             

                            Is that what you wanted?

                             

                             

                            EDIT: The key here is that you seem to be interested in finding both the distinct count and the rounds of samples allowed for each [Td Code] in the team, doing the calculation for that [Td Code], then summing the results. The LOD INCLUDE expression tells Tableau to do the calculation for each [Td Code], then wrapping the expression in a SUM tells Tableau to sum the result over all [Td Code] values.

                            2 of 2 people found this helpful
                            • 11. Re: LOD Expression and ATTR
                              Dereck Melo

                              Oh man!!!! You know how big of a smile was put on my face when it worked on all my sheets!!!

                               

                              Thanks so much Stephen and everyone who cares about the little people.