8 Replies Latest reply on Apr 11, 2018 4:11 PM by Anvesh Chinthala

    Calculated Field

    Anvesh Chinthala

      Hello,

       

      I like to show value as 1 for each line number of an order ID, even for null values too but,

      If a line number is having both Null and string together (Null and SLVR), I like to sum it as only 1

      and finally, I like to show sum of all values as grand total.

       

      In sheet 2, I made bubble chart but currently numbers aren’t accurate and when this problem is solved I like to show in chart.

       

      I created a calculated field called ‘Case Expression’ looks like it’s not working

       

      I attached a workbook

       

      Thank you for your help

        • 1. Re: Calculated Field
          Joe Oppelt

          Depends on what you really want to do here.

           

          In the attached I changed your CNT() to CNTD() and now you get 1 in every row.  But the grand total is finding 27 distinct row numbers overall.

           

          Do you have to see a 1 in every row for some reason?  I created a FIXED LOD calc that tells you how many unique line numbers an order has,  I displayed it on the sheet.  You get a proper value of 57 in the grand total.

           

          As for the Case Expression, for Order ID 60012290 you get 11 for the value because there are 11 rows of data for SLVR under line number 82.  I changed the SUM to AVG and I think that's what you want to do there.


          See attached.

          • 2. Re: Calculated Field
            Anvesh Chinthala

            Hello Joe,

             

            I like to see 1 in every row only if you could please.

             

            Grand total is good its 57, thanks for the formula

             

            This helps me a little bit but the problem is when put the fields 'Revision and 'lines per order' in bubble chart the numbers aren't accurate.

            Total for Gold should be 3, Slvr is 4 and Brnz is 1 and the total null values should be 49 after eliminating nulls which are attached to gold and slvr.

             

            I am attached an updated workbook please see the bubble chart in it.

             

            Thanks for your help!

             

             

             

            • 3. Re: Calculated Field
              Joe Oppelt

              I created a new calc called [Lines per order/line#].  This does the counting at the line number level, not at the OrderID level.  I inserted it in Sheet 1, and added it to TEXT on the bubble chart.

              • 4. Re: Calculated Field
                Anvesh Chinthala

                Joe,

                 

                This looks great ! but one last thing, in bubble chart the blue bubble is the total should be 49 but it showing as 54.

                 

                In below Image, for Line number 82 their is Null and Slvr and I like to have zero for null and 1 for Slvr and same thing with Gold. So this gives me total null value as 49.

                 

                Out of 54, there are 5 nulls need to be zero so it gives the result as 49.

                 

                Thank you very much for your help.

                 

                • 5. Re: Calculated Field
                  Joe Oppelt

                  This is why I asked about having a on every row.  You're going to get a count for all the NULL rows if there is a 1 on it.

                   

                  I modified [Lines per order/line#] to put a zero on the NULL when you have more than 1 revision.  But now you have zeros on Sheet 1.  If you want it both ways, make two calcs.  Use one on Sheet 1 and the other on Bubble.

                   

                  To tell if we have more than one revision, I made a calc called [Revisions per line].  (You'll notice that I had to insert a space in the calc if the value was null because tableau doesn't count nulls when it does COUNT and COUNTD.)  then I used that calc in my change to [Lines per order/line#].


                  See attached.

                  1 of 1 people found this helpful
                  • 6. Re: Calculated Field
                    Anvesh Chinthala

                    Perfect!

                    Thanks a lot Joe.

                    I have workbook with same data which has blanks instead of Nulls, do you the same formula can be applied?? or do I need to change anything in formula?

                     

                    Thanks again

                    • 7. Re: Calculated Field
                      Joe Oppelt

                      Null and blank are different values and behave differently.  All the stuff I was doing in there checking for ISNULL would not work on a blank.  But in places where I am checking for NULL, you would need to check for " ".  Note that " " is a space, whereas "" is a null.  Play with the calcs step by step to make sure you are handling things correctly.  You can see that in Sheet 1 I put my intermediate calcs onto the sheet so that I could see how things were happening under various conditions.  Do that when you implement this in your actual data.  Just play with one calc at a time until you are confident that it's behaving the way you want it to.  Then move onto the next calc.

                      1 of 1 people found this helpful
                      • 8. Re: Calculated Field
                        Anvesh Chinthala

                        Replaced Blank as Null and applied the formula it worked

                         

                        Thanks for all your help, Joe