4 Replies Latest reply on Dec 9, 2013 1:16 PM by Adam Cavazos

    Calculated Fields

    Adam Cavazos

      I have a dashboard that brings in information from 3 different excel data sources. One is inventory, one is incoming product, and then sales. The formula I used for my calculated field, "As of Inv", is: sum([Inventory Qty]) + SUM([Sheet1 (TomatoTransfers.xlsx)].[Incoming Qty]) - SUM([Sheet1 (TomatoOrders.xlsx)].[Sales Qty])

       

      This works exactly the way I want it, but only when there is volume/qty in all three rows. Below is a perfect example- See GH Vine Ripe Calavo. When there was activity in Inventory, Incoming, and Sales, I did receive an "As of Inv" qty (for sizes Jumb, Xlrg, Larg, Med). However when there is no activity in one of the rows (in the example below its the "Incoming Qty" row) it does not return an "As of Inv" Qty (see sizes 4x4, 4x5, 5x5, 5x6).

       

      I'm sure this involves null(s). Any direction on how my formula needs to written?

      Thanks

      Inventory (as of).jpg