Report for yesterday

Marcel M

Hello,

I hope someone can help me.

Maybe I'm blind, but I can't find a way to make a Report for only yesterday,

I work on a big sheet (> 4,000 rows, 25 columns) with many different days in the column [Datum].

Now I want to make a report, that shows only the rows from yesterday. Not today, not further in the past.

I want to make life-charts from the data, so I don't want to open the report and change the date everyday.

Since [Datum] is a date-column, I have to select it in the "When"-field in the Report Builder.

 

I tried different things, but none of them worked:

 

-"is in the last 1 day(s)" and "is in the past

--> only "last 1 days" includes today

--> only "is in the past" includes all days in the past

--> There is no way to choose "AND" instead of "OR"

 

-Help column in the sheet (something like "IF([Datum]:[Datum];=TODAY(-1);"Yesterday")" )

--> I reach the cell limit 

--> the sheet gets very slow and chrashes all the time

 

-Help sheet to return all entries of yesterday

--> I reach the limit of cross-sheet-references

Andrée Starå

Hi Marcel,

Try something like this.

Add the formula below to a helper column in the sheet.

=IF(Datum@row = TODAY(-1); "Yesterday")

The same version but with the below changes for your and others convenience.

=IF(Datum@row = TODAY(-1), "Yesterday")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

You could also use a checkbox helper column instead of a text one.

=IF(Datum@row = TODAY(-1); 1)

The same version but with the below changes for your and others convenience.

=IF(Datum@row = TODAY(-1), 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Marcel M

Thank you for your reply.

The sheet crashes when i add a new column full of formulas.

I will see, whether there are columns or formulas in the sheet, that I can delete, but I doubt it.

I think it would be best, if there was a way to choose it in the report builder, since it wouldn't make the sheet even bigger than it already is.

I got rid of some cross-sheet-references today. I will try to solve this problem with a help-sheet tomorrow.

Brian W

This is not an ideal solution, but you could use "is equal to" and select yesterday's date. You would then need to manually change the date when you run the report on a new day.

Andrée Starå

Happy to help!

Have also looked at simplifying/merging the sheet?

I'd be happy to take a look and see if I can give any more advice!

Can you maybe share the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Either way, let me know how it goes!

Paul Newcome

When selecting criteria in the report builder, there is also the option of checking a box to EXCLUDE specific criteria.

 

I haven't looked recently to give you specifics on how to set it up, but that is also an option worth looking at to see if you can make it work.

 

Marcel M

I had three extra sheets to collect data from this main-sheet.

I managed to merge two of them and outsource three formula-columns from the main-sheet to the new collecting sheet.

Thanks to this, I was able to make a new helper column and the sheet is a little faster now.

Thank you for your help

Andrée Starå

Excellent!

Happy to help!