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

# Calculated Fields

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

• ###### 1. Re: Calculated Fields

Hi Adam,

One option would be to change the calculation to the following:

ZN(sum([Inventory Qty])) + ZN(SUM([Sheet1 (TomatoTransfers.xlsx)].[Incoming Qty])) - ZN(SUM([Sheet1 (TomatoOrders.xlsx)].[Sales Qty]))

Hope this helps!

-Tracy

• ###### 2. Re: Calculated Fields

I've run into similar problems in the past. This is probably because you are using one table as the primary table or the table that the other sheets are being left joined to. A work around is to create dummy 'volume' records from the other sheets and insert them into your primary data source. This makes the underlying data look ugly, but helps fill in the missing pieces.

• ###### 3. Re: Calculated Fields

Thanks Ben,

I’ll give it a try

Thanks again!

• ###### 4. Re: Calculated Fields

Hi Tracy- didn’t think of ZN, I’ll see if works

Thanks