4 Replies Latest reply: Nov 7, 2016 1:51 PM by Sarah Stefancies RSS

    TOTAL () Qualifier on networkdays ()

    Sarah Stefancies

      I am trying to create a running budget that changes based on filters and am running into a problem with networkdays().

       

      I have a variable set up for working days and holidays:

      vWorkingDays = NetWorkDays(min([Sheet-date]),max([Sheet-date]),$(vHoliday))

       

      This works when the number stands alone since it changes based on filters.  So if the user selects three months, the number changes from 2018 to 44.  The problem occurs when trying to use this number in a table.  I’m trying to add a column to a table that multiplies budget hours by the net working days:

       

      (Sum([db.Daily Hours]))

      *

      ($(vWorkingDays))

       

      vWorkingDays in that formula changes based on the line, in this case Activity Code:

       

      table1 11-7-16.png

       

       

      So in the above table, I’m trying to multiple by 36 for every line but it is multiplying by the number in the far right column, making the “Budget Hours” column operationally valueless.

       

      Usually in this scenario, the total() qualifier would do the trick, but that doesn’t seem to work on networkdays(). 

       

      Using set analysis with {1} won’t work because I do want the number to change with the date selections.

      I tried to change the working days to sum(($(vWorkingDays))) so that I could use the total() qualifier – that didn’t work.

       

      Is there another function that I can use to make NetWorkDays(min([Sheet-date]),max([Sheet-date]),$(vHoliday)) not change within a table?