Forum Discussion

MichaelZeppieri's avatar
MichaelZeppieri
Qrew Trainee
7 years ago

Is there a way to show null values in a Summary Report

I am trying to create a Summary Report that summarizes the number of items as a normal value, and I am grouping the columns by item creation date, combined by month.

The table generates as expected. The column headers for the table show January, February, March, April, etc. in correct date sequence, and shows the correct count for the number of items associated to each month.

I have one problem. If the item count for a month is zero, I want to show it as zero. For my table, the months of April and November have zero items, but the table does not show those two months as column headers, with a value of zero.

Is there a way to do this?
reports and charts
  • AviSikenpore1's avatar
    AviSikenpore1
    Qrew Trainee
    7 years ago
    There are two ways you could accomplish this.

    1. Create a new formula numeric field which basically convert the existing value into a numeric value even for now by doing a NZ. Use that new formula numeric field in your summary report and that should display.

    2. The other way to try is to Modify the field properties of the summary field to treat blank values as zero. Make sure that you are not filtering out any records in your summary table based on the field value being zero.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    @Avi. I don�t think your solution will work. The problem is that the summary report only summarize his records that exist and is not summarize records they don�t exist.

    So one solution could be to introduce a set of dummy records contrived to sort stay to the very bottom of the list which would show up for a zero value in all months. So you might need to load in 12 dummy records.

    If you report is currently counting the number of items, you can make a formula feel that would calculate to 1 for all of the real records but would calculate to a zero for the dummy records.

    Then rather than counting the record you would do a summary total of that account field where each record would have the value of one except for the dummy record which would have a value is zero.

    Then your summary report totals would still be correct as the final row in the summary report would have zeros all the way across it but in doing so would force all the months from January to December to appear.
    • MichaelZeppieri's avatar
      MichaelZeppieri
      Qrew Trainee
      7 years ago
      I am not sure how this would work. I have a table in which each row represents items by item creation date. The report I am trying to create is performing a count of how many items were created per month. Creating dummy items would generate noise in my items report.

      When I create this same report in PowerBI, there is a button you can click that says "include null values" and the system knows to associate a value of zero to the months for which there are no items. I was hoping there was a way to do this in Quickbase.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      I do not know of a solution which would not create some "noise" in that summary report.  All I can do is to offer suggestions to minimize the noise.
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      You could make this solution pretty smooth.
       - Create the check value field that calculates to 1.
       - Create an automation to run on the first of every month to create a record called 'NO ITEMS THIS MONTH' in your Items table, with a check value of 0.
       - Create an automation that runs any time an item is created in the Items table and the month of the date created of the new item matches the month of an extant 'NO ITEMS THIS MONTH' record, to delete the matching 'NO ITEMS' record.
       - As you alluded to, the real challenge would be updating any existing reporting on the Items table to add a filter to only include items with the check value of 1.

      I could see it working. Maybe not in your ecosystem.

      You could also create a uservoice suggestion to mimic the behavior you see in PowerBI. I have also had use cases where summarizing values that didn't exist would have been helpful.

      To quote Evan Martinez, Community Manager: "I would encourage you to submit feedback regarding this via our Feedback Platform - UserVoice - Which can most easily be accessed from the My Apps page in Quick Base by clicking on the orange Feedback tab or at http://quickbase.uservoice.com .  This forum is used by our development team to explore customer suggestions for enhancements / changes to the platform. Many of the items that have been suggested by customers have already been incorporated into Quick Base and I would encourage you to participate as well. If you do create a User Voice thread for this issue I encourage you to post it here so that other community users that agree can cast their vote as well."