I am trying to chart a running total for a dataset. Georgecollier kindly helped me do this in this thread.
Problem is this dataset contains holes and i want to plot the holes as well.
So the end result will show a running total for all dates even the ones with entry. These holes will be calcuated with zeros.
The problem with the solution in the previous thread is that it only plots the dates with entries and cannot plot the holes. Is the only way to do this by actually inputing data for every date?
Not sure how you set it up nor have I looked at the thread you posted but maybe try to include empty parameters? I think you’re only filtering for things that have data in them
I’m not at my computer but the solution here lies somewhere in having a format as text yes/no condition that when a number is zero we do something else. I think my solution has an issue which is that it could break with zeros because of duplicate number in the list. I don’t know, I’ll test maybe.
To not skip empty dates, you need to be Do a search for Data Type:grouped by Date (do not skip empty groups). Rest of the logic is the same (grouping’s aggregation number can be used in the cumulative sum logic)
Ok I tried grouping every list call but not getting the correct results. The running total is not correct for the zeros. I think the problem is the format to text and extract with regex because I no longer can input the current cells unique ID. Instead I can only input the groupings aggregation.
Could you maybe post a demo editor so I can see your logic with the groupings?
Actually, there is a problem. So the running total with empty spaces works. but the problem is that for some reason the aggregates are pushed back in date randomly. So the running total for the first data comes 7days earlier while the last data is pushed back as much as a month.
Update:
So I figured out the reason. The counter using the regex you made was giving multiples of 2 so needed to divide by 2. The reason for this is beyond me. Could you explain?
That’s not the reason for your issue. The regex is supposed to extract a unique ID. It then counts the number of commas before that unique ID to work out the item # in the list. You can find a way to make each grouping have a unique ID (e.g display the grouping’s data’s unique IDs in a concatenated list)
