5 Replies Latest reply on Jan 15, 2016 2:40 AM by Darshan MS

    Maximum date record display

    pallavi pande

      @Hello Guys

      Need some help..

       

      I have a data set like below for the operations 80, 90 and 95 there are two records.

       

      First record is with date 20141025 and other is 20150810 (latest)

       

      I want a formula which will give me only one record  where the only row is displayed for 80, 90 and 95.

      The record should be of latest date. It is not necessary for me to show AEDAT field in the report. Only one row should be shown.

       

      Existing data is as below.

       

      Material
        No
      Aedat001000200030004000500060007000800090009501000110
      566662020141025ENG-CENGIPS-CIPSLASER-CLASERTHRDFORM-CTHREADWASH-CWASHHONE-CBLASTWASH-CWASHSTAGE-CSTAGESUBCOAT-CSUB LABEL-CPACKPACK-CPACK
      20150810 PVDLOAD-CPVDLOADPVD COAT - HPN1-CPVD6

      PVDUNLOAD-CPVDUNLD

       

       

       

      Expected output data is as below.

       

      expected
      Material No001000200030004000500060007000800090009501000110
      5666620ENG-CENGIPS-CIPSLASER-CLASERTHRDFORM-CTHREADWASH-CWASHHONE-CBLASTWASH-CWASHPVDLOAD-CPVDLOADPVD COAT - HPN1-CPVD6PVDUNLOAD-CPVDUNLDLABEL-CPACKPACK-CPACK
        • 1. Re: Maximum date record display
          Mark Fraser

          Hi Pallavi

           

          First thing - do you have v9? If not, and you're able, upgrade.

           

          Assuming you do have v9, you can use LoD calculations, so something like this

          {FIXED [Order ID]: MAX([Order Date])}

          Using that formula, would return like this, its returning the highest date for the same order

          You can then extend it, like this

          IF [Order Date]=[Calculation1] THEN 'SHOW' ELSE NULL END

          This will ensure you only return the max date for each Order.

           

          For your example, you need to swap Order ID to Material No (I assume) and Order Date to AEDAT (you may need to make this field a date first)

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Maximum date record display
            Darshan MS

            Hi Mark,

             

            Thanks for a reply!

             

            After using the Calculation1 and Calculation2 as follows,

             

            Calculation1.png

             

            Calculation2.png

             

            And inserted the above two formulas in ROWS section as follows,

             

            Scenario 1.png

            In the above screenshot,

             

            - it is observed from Calculation1, it has selected the maximum date of AEDAT for the respective Material No.

            - it is observed from Calculation2, it has showed the NULL for the minimum date and SHOW for the maximum date among AEDAT.

             

            Filter by Calculation2.png

             

            In the above screenshot,

             

            - I have filtered by Calculation2 to hold 'SHOW'

            - The result suppressed the operation 10 to 70 and 100, 110 (Because it contained a date which was minimum) - Please refer Pallavi's post "Existing data as below"

            - To understand it clearly, I have filtered Calculation2 to hold 'Null' and the screenshot is as follows,

             

            Filter by Calculation2 V2.png

             

            Here, it fetched Operation 10 to 70 and 100 to 110, but suppressed the 80, 90 and 95 since it had maximum date where the data of operation exists.

             

            Please refer "Existing data" and "Expected output" screenshots of Original post.

             

            In summary,

             

            Expected movements.JPG

             

             

            In the above, the scenario with AEDAT is as follows, but if I remove the AEDAT drilldown, the tableau output should overwrite as above pointers (The green ones are entries for operation on AEDAT 20150810 which is recent for 80, 90 and 95. And for others 20141025 is recent).

             

            Hence the expected output is as follows,

             

            Final.JPG

             

            Please provide a suggestion on the above. Thanks!!

             

            Best Regards,

            Darshan MS

             

            Re: Maximum date record display

            • 3. Re: Maximum date record display
              Mark Fraser

              Hi Darshan

               

              Thank you for the detailed explanation of the problem, I hope I understand -

              You wish to blend the results from multiple lines, overwriting if values are present in the latest date value and keeping the results from the earlier line if no updates are present?

               

              Hmmm... this isn't really a task intended to be done in Tableau, Id be more comfortable using SQL or Excel, it would be much easier.

              It may possible with a combination of LoD, IF and LOOKUP but I have never tried.

              Leave it with me, I'll have a think/ play around.

               

              In the mean time, maybe someone else has developed something they can share... or likes a challenge!

               

              Cheers

              Mark

              • 4. Re: Maximum date record display
                Darshan MS

                Hi Mark,

                 

                Yes, your understanding is correct.

                 

                Thanks for your help, looking forward for suggestions from you and other Tableau developers in the community.

                 

                Best Regards,

                Darshan MS

                • 5. Re: Maximum date record display
                  Darshan MS

                  Hi Everyone,

                   

                  Please provide the suggestion on how to achieve the resolution to this issue. Thanks!!

                   

                  Best Regards,

                  Darshan MS