Collect record for previous day to Calculate variance

Shile

Main problem: I am attempting to collect the record of Daily absent for the previous day, I get incorrect argument error. I could use any help.

My end goal is to use the previous day record against the daily absent record to get the variance in numbers and percentage. I would appreciate any other intuitive way of doing that, if there is any.

Thanks

Kelly Moore

Hey @Shile

=JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))

Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.

You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.

cheers

Kelly

Kelly Moore

Hey @Shile

=JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))

Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.

You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.

cheers

Kelly

Ordep

Hi Kelly, thank you for your post.

But how do I solve the situation where there is no "Date@row-1"

Eg.: The date goes directly from Dec 12th to Dec 18th. This way Date@row-1=17th (of which there is no register.

I have a column of dates that, but sometimes there gaps in those dates and corresponding data in the data column.

I nevertheless want to automatically (formula column) calculate the diference between the most recent regsiter of data with the previous register of data (that is not necesarily the day before the current register date, but the most recent date).

Kind regards and a great weekend

Kelly Moore

Hey, sorry for the delay

Will this formula work for you? It would replace my original formula

=INDEX([Daily Absent]:[Daily Absent], MATCH(MAX(COLLECT(Date:Date, Date:Date, <Date@row)), Date:Date, 0))

Kelly