Skip to main content

counting last 5 days

i'm looing for somthing that keeps counting the last 5 days per id.
so from the fifth row, while the id is the same, i add the "amt" but if the "id" changes, it starts again.
for ex.
 

Brian Element Tue, 07/04/2017 - 07:42

Hi Joshua,

Interesting problem, I should be able to figure it out in a script.  Thanks for posting an image as it helps when trying to create something.  I will get back to you when I have something for you to try.

Brian

Brian Element Tue, 07/04/2017 - 10:02

Hi Joshua, for your question, is it possible that you can have the same date multiple times for the same ID? or will it always be a separate date per item?

Brian Element Wed, 07/05/2017 - 09:19

Hi Joshua,

I have something for you but I am waiting to hear if there can be multiple dates for the same ID.  If there is then that complicates the script a fair amount so let me know.

Thanks

Brian

Joshua Tue, 07/11/2017 - 07:43

hi Brian,
weird.. i'm almost sure i posted a comment here but i don't see it anymore :|
for every ID there's only one row per date.
the idea is to check for every ID , for the all year (jan- dec ) the amount for seven days in a row ( i notice i wrote 5.. but i ment 7 days )
sorry for the delay and thank you for your help
Joshua

Brian Element Tue, 07/11/2017 - 08:08

In reply to by Joshua

Hi Joshua,

Thanks for getting back to me and the clarrification.  I will get back to you when I have the script ready.

Brian

Brian Element Thu, 07/13/2017 - 05:07

Hi Joshua, just to let you know I haven't forgotten about you.  Work has been quite busy and I haven't had any extra time to complete this.

Brian Element Sun, 07/16/2017 - 09:50

Hi Joshua,

Here is a script for you to try out.  You have to select the ID, the date fields (needs to be in date format) and the amount.  It creates a sorted database and then calculates the field based on the id and date field.  Let me know if you find any problems with it.

Thanks

Brian

Joshua Sun, 07/16/2017 - 10:38

Thank you Brian!
I get this error and the cursor is where the yellow line on the script
Thanks,
Joshua

Brian Element Sun, 07/16/2017 - 19:40

Hi Joshua,

I updated the file, I forgot to change the hard coded field names for the variables.  I changed that so it should work for you now.

Brian

Joshua Mon, 07/17/2017 - 01:44

Hi Brian,
it appears that the "sum" field doesn't do the job well.. look at the image..
on the fifth row the "sum" starts again (instead on the 8 row sums rows 2-8, and  row 9 sums 3-9 and so on..)
 
Thanks again for all your help!!! :)