none
Please Help a Newbie Out with Urgent Question...... Rolling Sum RRS feed

  • Question

  • So i have a table with a start date and an end date for when cases where opened or close. I need to show in my report for any start date, how many cases are still open (ie end date > start date or end date =blank) and then get a rolling sum of cases still open.

     

    so far this is the approach that i have taken

    i created a new column assigning a 1 to end date >start date else 0 (open cases 1

    i created another colum assigning a 1 to end date =blank else 0 (open cases 2)

    i created a column assigning a 1 to enddate <> blank (closed cases)

    i created a column suming open cases 1+open cases 2 (total open cases)

     

    what i need is the Total still Open Column

    Date - Total Open Cases - Closed Cases - Total still Open

    Jan 1 -  1                             0                            1

    Jan 2 -   5                             3                            3 (5-3+1)

    Jan 3 -   10                            12                         1 (10-12+3)

     

    Friday, May 25, 2018 3:45 PM

Answers

All replies