1 2 Previous Next 15 Replies Latest reply on Jan 16, 2018 2:59 PM by Shinichiro Murakami

    Creating Consecutive Totals with Missing Dates

    matthew Blanch

      Hello,

       

      I have a bit of an issue with a data set and was looking for some help.

       

      I have a data set that involves counting the consecutive days an ID occurs so i can flag the amount when it surpasses a limit.

       

      My Data will have a date for every ID. However, not every ID has a date. So in Some cases i have the following:

       

       

      I've tried to do a running Sum with several iterations of If statements but have had no luck,

       

      I have even tried a fixed LOD to count the ID's daily such as

       

      { FIXED [Asset ID],[Reference Day]:COUNTD([Asset ID])}

       

      And have then tried to use an if statement to count them and reset if the value was 0 but was unsuccessful.

       

      I understand that in some cases the date is not present in the table such at day 15 here.

       

      So what i want to do is store the counts up to the "missing day" as technically this "should be" 0 and then create a new total for the next set of consecutive days. I want to store each consecutive batch of totals per AssetID and then i can evaluate by ID if it has exceeded a certain limit, lets say 5 consecutive days in this case, at any point in time.

       

      Is there an easy way to do this without trying to pad the data for the missing dates. If not, and assuming i did pad it, what would be the approach? I've attached a copy of the workbook

       

      Thanks - appreciate any help!

        1 2 Previous Next