4 Replies Latest reply on Feb 12, 2019 1:42 PM by Deepak Rai

    How to Use Values from Previous or Next Rows

    Nitya Goriparthi

      Hello,

       

      Currently I'm using SQL 'LEAD' function to find out when my product is 'back_in_stock'.

       

      How do I do this in TABLEAU ?: I want to create a report which shows Dates of when the product is 'out of stock' and when it's 'back in stock'. How do I get 'Back_in_Stock' column in tableau ?

       

       

      SELECT

             [CustomerKey]

            ,[ProductKey]

            ,[DateKey]

            ,[Batch Number]

            ,[BatchKey]

            ,[Valuated Unrestricted Use Stock]

            ,CASE WHEN quanity_diff < 0 THEN date_next else null END as back_in_stock

      FROM (

                SELECT

                        [CustomerKey]

                       ,[ProductKey]

                       ,[DateKey]

                       ,[Batch Number]

                       ,[BatchKey]

                       ,[Valuated Unrestricted Use Stock]

                       ,LEAD([Valuated Unrestricted Use Stock] ,1,0) OVER (PARTITION BY [CustomerKey], [ProductKey],[Batch Number] ORDER BY DateKey) as quantity_next

                       ,LEAD(DateKey,1,0) OVER (PARTITION BY [CustomerKey], [ProductKey],[Batch Number] ORDER BY DateKey) as date_next

                       ,[Valuated Unrestricted Use Stock] - LEAD ([Valuated Unrestricted Use Stock] ,1,0) OVER (PARTITION BY [CustomerKey], [ProductKey],[Batch Number] ORDER BY DateKey) as quanity_diff

           FROM [CornerstoneDW].[dbo].[factFreezerHistory]

       

      ) A

       

      FREEZER.PNG

       

      Any help is appreciated ! Thank you!!