13 Replies Latest reply on Jan 30, 2019 5:02 PM by Michael Daddona

    Using Lookup to fill in missing data rows with 0

    Alison Dayton


      I have been searching how to fill missing data with 0. I have combinations of regions, markets, and provider types that have data for each month.

      For example,

      Region - West, Market - California, Provider Type - Physician has 12 total submissions for the month of 11/18, 7 submissions for 12/18 and 15 submissions for 1/19

      Region - West, Market - Hawaii, Provider Type - Facility has 103 total submissions for the month of 11/18 and so on...

      Region - Southeast, Market - Florida, Provider Type - Physician has 23 total submissions for the month of 11/18 and so on...


      Sometimes, there is a combination that does not have any submissions for a certain month. When that is the case there is no row in the source data with that combination.

      Therefore, when I am creating my line graphs and forecasting future submissions there are too many missing values. The missing values need to be zero.


      I have read about using ZN() and Lookup() to make this happen but I have not had any success. When I use these to create a table calculation it says I cannot forecast with a table calculation. All of the examples I have seen use these functions in conjunction with a table display. I am trying to create line graphs of the data by month and compute any missing data rows to 0.


      I have considered modifying the data at the source, but there is about 4 years of monthly data, 4 different regions, roughly 8-15 markets per region and three different provider types for each region/market combination. That would be a LOT of combinations to check and update. Also, since this is forecasting, we do not want to have to manually add rows in the future. It should all be automatic. So we would have to use a stored procedure to create a temp table with all of these combinations and then perform a join... the stored proc would take an awful long time to run which wouldn't be efficient.


      Workbook attached is fake data and on a much smaller scale than the actual data. There are actually 5+ regions, and 20+ markets and 3 years of historical data.