I have a dataset which gets new data everyday. It has the format date, amount. I always want to show the last 7 days, excluding today. When I select them, the selection is static and keeps on showing that specific part. Is there a function which will let me select the last 7 days, excluding today?
Date
Votes
7 comments
-
Yes! You can use the SELECT function along with some of our date and time functions to filter data for the last seven days. Assuming your dates are in column A and your amounts are in column B of your data source, your formula would look something like this:
select(@B:B;, BETWEEN(DATE(@A:A;, "<your_date_format>"), DATE_ADD(TODAY(), "5", -7), YESTERDAY()))
Where you'll need to replace <your_date_format> with the date format of the dates from your data source. See more information on date formats here: https://support.klipfolio.com/hc/en-us/articles/216182377-Using-Date-and-Time-Formats
Also see this article on filtering by dates which covers this and other date range scenarios: https://support.klipfolio.com/hc/en-us/articles/215548318-Filtering-by-date-range
-
Thanks a bunch! I'm going to try it out tomorrow. For now I fixed it using SLICE(column, -8,-1), which seems to work as well :)
But your way seems more specific, so I'll be sure to experiment with it.
-
Perfect! If your data is always added to the bottom of your data source and always up to date, then the SLICE method will work as well. As always, there's usually more than one way to accomplish what you want to do in Klipfolio :)
-
Hi Trish,
How about excluding the first and last day of the month?
-
Hi Geae,
There are a few different ways to exclude the first and last of the current month you using our Date/Time functions. Here's one approach:
BETWEEN( DATE(@A:A;, "date format"), DATE_ADD(DATE_STARTOF( TODAY(), month), day, 1), DATE_ADD(DATE_ENDOF( TODAY(), month), day, -1)) )
The Date/Time functions in this example are used to add a day to the start of the month for the starting bound of the BETWEEN and to subtract a day from the end of the month for the ending bound of the BETWEEN.
Note that we now have a Actions menu to help simplify your formulas. You can filter, group & aggregate and sort your data instead of using functions like SLICE, SELECT, GROUP and SORT.
Thanks,
Janice -
Hi Geae,
Assuming your data source has data for the current month, you could use this formula to select all the dates of the month, but not the first day of the month or the last day of the month.
select(@B:B;, NOT(OR(DATE(@A:A;, "yyyy-MM-dd")=DATE_STARTOF(today(), month), DATE(@A:A;, "yyyy-MM-dd")=DATE_ENDOF(today(), month))))
Alternatively, if your data source always has the first day of the month in row 1 and continues in rows until you have the last day of the month in the last row you could use a simpler SLICE formula. This formula simply removes the top row and the bottom row from your data source.
SLICE(@B:B;, 1, -1)
-
For Customer life time value data i saw that the formala for data on the Avg Life time value is
("1"/(slice(@Sheet1,7:7;,"-1")/slice(@Sheet1,8:8;,"-1")))
could you explain how this formula works for sample data
Please sign in to leave a comment.