BLANK values from no transaction data

BLANK values from no transaction data

Hi all,

Having an issue with what should be a seemingly easy report, but keep coming up against roadblocks.  Hoping someone has troubleshooted this.

Goal is to have an easy, quick glance report for Buyers/Sales of a full list of inventory: "Item", "Item Desc.", "Physical Qty on Hand (from query)", "Committed Inventory (from query)", Available for Sale (Physical Qty - Committed), "Qty Incoming"

The issue is that when there is no transaction data, the queries don't retrieve anything for that item so it's ignored.  IE: Item A has never been on a sales transaction, so the Committed Inventory query does not see it.  This results in blank values on the summary report against items that have transactional data under purchases.  Which would be fine if it could return 0 instead of blank.  So instead of (25-blank) = blank it would compute as (25-0) = 25

I thought I could use a sum(if_null("columna",0)-if_null("columnb",0) formula in the table but it also gives a blank if one of the columns is blank.

At this point I'm going around in circles trying to figure out an easy solution, hoping someone can offer some advice on the best way to solve this.

            Zoho Marketing Automation

                    Zoho Pagesense Resources

                      Zoho SalesIQ Resources

                            Zoho TeamInbox Resources

                              Zoho DataPrep Resources

                                Zoho CRM Plus Resources

                                  Zoho Books Resources

                                    Zoho Subscriptions Resources

                                      Zoho Desk Resources

                                        Zoho Projects Resources

                                          Zoho Sprints Resources

                                            Qntrl Resources

                                              Zoho Creator Resources

                                                Zoho WorkDrive Resources

                                                    Zoho Campaigns Resources

                                                      Zoho CRM Resources

                                                            Design. Discuss. Deliver.

                                                            Create visually engaging stories with Zoho Show.

                                                            Get Started Now