13 Replies Latest reply on Sep 13, 2016 5:23 AM by Tom W

    LOD Calculation Field Problem?

    Sandeep Kotha

      Hi All,

       

      I have below pivot table and I want the same count in Tableau using LOD calculation.I want the count of records where fielcCount=3 which indicates the ids who are in all the three months.

      The fields are:

      Month2, Id, Losses.

       

       

       

       

       

      I have written the code as below:

      If { FIXED [Id]:COUNTD([Month2]) }=3 then [Losses] end But it is not giving the correct values.

       

      Could someone please help me with the correct code for my output?

       

      Best Wishes,

      Sandeep

        • 1. Re: LOD Calculation Field Problem?
          Tom W

          Hi Sandeep,

          We need more details.

          • Please upload a sample Tableau Packaged Workbook.
          • What exactly do you mean it isn't giving correct values? Is it over calculating? Undercalculating? Please provide an expected example as it relates to your sample.
          • 2. Re: LOD Calculation Field Problem?
            Sandeep Kotha

            Hi Tom,

             

            Attached is the workbook.My output should be as below:

             

             

            Joined in Nov'151052
            Joined till Jan'152025

             

             

            But the tableau output is:

             

            Please help me out with the correct code.

             

            Best Wishes,

            Sandeep

            • 3. Re: LOD Calculation Field Problem?
              Tom W

              Sandeep,

              It needs to be a Tableau Packaged Workbook with datasources extracted, otherwise I cannot view / connect to the data.

              • 4. Re: LOD Calculation Field Problem?
                Sandeep Kotha

                Hi Tom,

                 

                Please find the same.

                 

                I had tried the below code as well. But did not worked.

                 

                if { FIXED [Bucket]:COUNTD([Month2]) }=12

                then [Losses] END

                 

                Any luck!

                 

                 

                Best Wishes,

                Sandeep

                • 5. Re: LOD Calculation Field Problem?
                  Tom W

                  Firstly, please read the suggested reading on the getting started page for advice on attaching Tableau Packaged Workbooks and extracted data for future posts as it's much easier to help when you attach a TWBX correctly.

                   

                  Secondly, what you're asking for in your original post doesn't seem to match what you're asking for in your subsequent posts.

                  This Level Of Detail calculation will break out the number of ID records which have 3 or more matching 'Month2' records;

                   

                  if { FIXED [ID]:COUNTD([Month2]) }>=3

                  then 'At least three months'

                  else 'Not Three Months' end

                   

                   

                  However, then you seem to be asking in your second post for the number of people who joined in a specific month?

                   

                  Can you please outline exactly what you're trying to achieve, along with expected numbers? Are you using the bucket field? Are you using the YesNo field?

                  • 6. Re: LOD Calculation Field Problem?
                    Sandeep Kotha

                    Hi Tom,

                     

                    Let me brief once again what I want.I have attached the excel file.It consists of 3 sheets.

                    1. "Data"

                    2."Pivot Table"

                    3."Output"

                     

                    I have framed the pivot table using the data that I have and put it in "output" sheet which is the final output that I want.For this , I have created field called "Similar" as shown below:

                     

                     

                    Field Similar:

                    if { FIXED [ID]:COUNTD([Month2]) }=12 then "All 12" else "Different" end

                     

                    My requirement is in the "Output" Sheet.Now I want the count as 25 which is in the "output" Sheet. But I am getting the wrong values. May be, I have not coded correctly.

                     

                    Hope my requirement is clear now. Please let me know if you need any further details.

                     

                    Thank you.

                     

                    Best Wishes,

                    Sandeep

                    • 7. Re: LOD Calculation Field Problem?
                      Tom W

                      You didn't attach an excel sheet.

                      I hope the excel file is detailed enough, but you still haven't explained why the result isn't correct. Have you looked at the row level data in tableau to make sure records are actually being classified correctly by the LOD calculation?

                      • 8. Re: LOD Calculation Field Problem?
                        Sandeep Kotha

                        Hi Tom,

                         

                        Here it is the attachment.

                        • 9. Re: LOD Calculation Field Problem?
                          Sandeep Kotha

                          Hi Tom,

                           

                          I have attached the excel document. Please let me know if u need any information.

                          • 10. Re: LOD Calculation Field Problem?
                            Tom W

                            Your excel attachment does not contain the three sheets you said it does.

                             

                            Here's what I need you to do if you would like me to help you;

                            1. Create a smaller subset of your data with less rows so you aren't looking at 11MB attachments.

                            2. With that data as your basis, connect to it in Tableau and setup the packaged workbook correctly to use an extract, then save the workbook as a Tableau Packaged Workbook. Include your calculations in this workbook.

                            3. Provide a breakdown of your expected results and how you've arrived at those.

                            • 11. Re: LOD Calculation Field Problem?
                              Sandeep Kotha

                              Hi Tom,

                               

                              1. Please find the attached excel file with 3 tabs.

                              2. Please find the attached tableau Packaged Workbook with calculation fields.

                              3.I have got the number of records where Count=12. I got the value as 3.

                               

                              My question is that I am implementing the same for a huge data, the value is not matching. This is the issue.

                               

                              Please let me know If I still miss anything.

                               

                              Best Wishes,

                              Sandeep

                              • 12. Re: LOD Calculation Field Problem?
                                Sandeep Kotha

                                I am attaching the large Sample where the count is not matching with the excel count.

                                 

                                Excel count=1052 and tableau Count=1079.

                                I am doing the same steps as I have done for the small sample.This is what the issue.

                                Not sure what went wrong.Please let me know?

                                • 13. Re: LOD Calculation Field Problem?
                                  Tom W

                                  Simply doing a high level count of the records and saying "they are different" isn't enough to identify the issue. When I say you need to provide expected results versus actual, you need to dig in to the data and find where it's different. You've got two different counts of ID's, so you need to take a list of the ID's from Excel and compare them to a list of ID's in Tableau.

                                  Modify your sheet in Tableau and ad the 'ID' to the Row Shelf. You should now have a list of ID's in Tableau which you can use to compare to your Excel example. Which ID's in Tableau are being classified incorrectly versus the Excel sample? Once you isolate a couple of those, it's pretty straight forward to look at the data.