5 Replies Latest reply on Mar 22, 2014 8:48 PM by vishwanath Pendyala

    Inventory opening balance calculation

    Andrew Murphy


      Hi

       

      I am trying to create a calc to work out the opening balance for a product in a partiulcar time period (similar to firstnonempty in ssas).

       

      We have our data stored at a product, date level, which contain the balance figure for each day in the month

       

      I have found this very difficult and could not find any examples to follow.

       

      I have managed to make a calc that finds the min date for each item for each month, but cannot see how to use this to lookup the balance figure.

       

      Currently, we have deployed a workaround as follows:

       

      iif(day([dtDayDate])= [FIRST_DAY_OF_MONTH], [nbrOpenSOH], 0)

       

      This assumes that the first day of the month is the opening balance, which has impiications as there may not always be stock records for all products on the frist day of the month

       

      Any help would be greatly appreciated on this.

       

      Many thanks in advance.

       

       

      Andrew

        • 1. Re: Inventory opening balance calculation
          vishwanath Pendyala

          Jonathan Drummey

           

          Is there any way to get the First NonNull valuein Tableau

           

          Thanks

          Karthik

          • 2. Re: Inventory opening balance calculation
            Jonathan Drummey

            There are several levels of granularity to deal with in Tableau, the two key ones being the level of granularity of your data and of the Tableau view. The latter is determined by the dimension(s) present in the view (on Rows, Columns, Pages, and Marks Card). Without a sense of what the view looks like, I can't give an exact answer to the question. That's part of why we usually ask for a Tableau packaged workbook with sample data that represents your situation, and ideally a mockup of your desired results. For an explanation why, see http://community.tableau.com/docs/DOC-5065.

            • 3. Re: Re: Inventory opening balance calculation
              vishwanath Pendyala

              Jonathan Drummey

               

              I have built some sample data and was figuring out this solution from a couple of hours , I wanted to some how have interest in knowing the way to get it done .

               

              Thanks

              Karthik

              • 4. Re: Re: Re: Inventory opening balance calculation
                Jonathan Drummey

                Hi Karthik,

                 

                You wrote that you wanted to know "...the way to get it done." There are usually multiple routes to a solution in Tableau, and the exact route that one might use is entirely dependent on the situation. In the attached workbook are two ways using your sample data and the view you'd built. One uses a conditional filter to get rid of all the Null values of Price for each product/date, then a table calc to return the first date, the second uses a more complicated table calc to identify the date for the first non-Null value of Price. There are at least a couple of other techniques that could potentially work, a self-data blend or Custom SQL/custom query.

                 

                However, as I'd said, without knowing what dimensions are needed in the view and a better sense of the underlying data, I can't give an exact answer. For example, some people have wanted to find the first non-Null value but have had a crosstab layout where Tableau is generating a bunch of empty cells/panes, so it looks like a bunch of Null values where there is actually no data. Here's an example, using Category and Order Date from Superstore Sales:

                 

                Screen Shot 2014-03-22 at 8.53.43 PM.PNG.png

                 

                It might look like there's a Null value for Appliances for 1/1/2013, there's actually no data there. Tableau is only drawing a cell because of the pills on Rows & Columns, there's no data for Appliances until 1/6/2013 where Tableau shows us there's data with the the first Abc. So until I see some sample data and a view, I'm not willing to say I have a potential solution.

                 

                Jonathan

                • 5. Re: Re: Re: Inventory opening balance calculation
                  vishwanath Pendyala

                  Hi Jonathan

                   

                  Thank you allot for enabling me to think in different perspectives to approach for a solution and explaining them clearly , Yes I agree with you.

                   

                  Thanks

                  Karthik