spreadsheet template question - automation

[mykrobinson]

I need to create a bot to send a report showing places the marketing/outreach team has visited 7, 14,  30, 90, and 160 days ago. Seems easy enough, so i started crafting the bot, making a different task for each of those timeframes, then it dawned on me, I wonder if I can send a report to do all of this in one step and just separate the sections...

My current template looks like this:



Can i just add another row under the second one with a different filter start and end? 

[Suvrutt_Gurjar]

Your START expression could be something like 

<<START: ORDERBY(FILTER("Visitation Log", IN([Date] , LIST( TODAY()-7 ,  TODAY()-14 , TODAY()-30 ,  TODAY()-90 , TODAY()-160))), [Date], FALSE)>>

I believe the spreadsheet template does not allow  any sections etc. So the above report will sort the records by dates in ascending order but there will not be any sections for different dates.

You could possibly add a simple VC that shows the aging of record, something like 

SWITCH([Date], TODAY()-7, " 7 days" ,  TODAY()-14, "14 days" ,....... , TODAY()-160, "160 days",  "Other days")  and include it in the report to quickly identify record aging.

[mykrobinson]

that is a thing of beauty right there. I will try, thank you. I am still learning to think in this manner, i'm sure it will come with more time and exposure.

[SkrOYC]

I suggest you to use multiple worksheets if it fits your need.

So you can have just one workbook with all of the data separated on worksheets for each section.

[mykrobinson]

oh, i like that! Once again, a possible path that i did not even consider. thank you!

[SkrOYC]

---

@Suvrutt_Gurjar wrote:

SWITCH([Date], TODAY()-7, " 7 days" ,  TODAY()-14, "14 days" ,....... , TODAY()-160, "160 days",  "Other days")

---

BTW, when making the validation/filter expression of a SELECT() expression (or similar) you could add an expression like yours without the need of a VC, this is part of the things I started doing after my App reached 1 min sync time. Maybe I'll post something about it in the future.

This may not be new to you, I'm just adding comments in case it helps someone. With the VC you would do a simple thing like this:

FILTER(
 "TableName",
 [Virtual column]="14 days"
)

 If the VC is like the one you provided:

SWITCH(
 [Date],
 TODAY()-7, "7 days",
 TODAY()-14, "14 days",
 TODAY()-n, "n days"
 "out of range"
)

You could have this inside the original expression, like this:

FILTER(
 "TableName",
 SWITCH(
  [Date],
  TODAY()-7, "7 days",
  TODAY()-14, "14 days",
  TODAY()-n, "n days"
  "out of range"
 )="14 days"
)

 Or, in this case:

FILTER(
 "TableName",
 CONCATENATE(
  FLOOR(
   TOTALHOURS([Date]-TODAY())
  ),
  " days"
 )="14 days"
)