2 Replies Latest reply on Oct 20, 2015 6:15 AM by Patrick Lefler

    Viewing Sum and Running Sum where some values are null and the date is filtered

    Patrick Lefler

      I have a data table that lists sales of five (5) particular items each month. If an item is not sold that particular month, there is no entry for that item (it is 'null').

       

      I think I figured out a way to eliminate the null values so that when I look at the data, I see sales numbers (or zero) and running sales amounts for each item. (see Workbooks 2 & 3). The problem arises when I try to filter the view so that I look at just one month. In this case, the view reverts back to just the particular items that had sales that month - eliminating from view not just the items with no sales that month, but also eliminating from the view the running sum for each item. (See Workbooks 4 & 5)

       

      I'm using the ZN function for both the calculated field C:ZN-SUM(Sales)      ZN(LOOKUP(SUM([Sales]),0))

       

      and the Table Calculation C: ZN - RunningSum(Sales)       ZN(LOOKUP(RUNNING_SUM(SUM([Sales])),0))


      Again, the goal is to be able to view all five items in my table no matter if they had sales or not when the month is filtered.

       

      Any assistance would be greatly appreciated.


      Pat