Look back dynamically

[Yuri24]

Hello,

I am trying to calculate rate based of product site visits and products purchased which will be products purchased in that last 15 days over product site visits for the same time frame. However, the 15day look back timeframe should be dynamic for any date selected.

I wrote a condition to filter the data which is [SiteVisitDate]>=DatetimeAdd(tDateTimeToday(),-15,"days") and [SiteVisitDate]<=DateTimeToday() but this seem to capture or filter data for today only- and when I link the output to a dashboard and select a date from drop down this is not pulling data correctly.as it has data limited to last 15 days. So when I select yesterday's day I am not showing full data.

Is this possible to do this dynamically for any date?

Sample data

SiteVisit	Purchased	SiteVisitDate
1	0	02/17/2023
1	0	09/16/2022
1	0	05/24/2023
1	0	09/06/2022
1	0	08/26/2020
1	1	01/03/2023
1	1	08/01/2023
1	0	06/23/2023
1	0	09/11/2023
1	1	04/20/2023
1	0	05/09/2023
1	0	07/28/2023
1	0	11/15/2022
1	1	11/08/2022
1	0	08/05/2023
1	0	05/04/2022
1	0	10/21/2022
1	0	11/17/2022
1	0	07/06/2021
1	0	10/03/2022
1	0	09/21/2022

[Peachyco]

This is kinda a simplistic, brute-force approach, but this can be done with the Multi-Row Formula Tool by looking through the previous records and checking if they're within 15 days of the current record, summing the numbers along the way:

 

[PangHC]

---

@Yuri24 wrote:
...

when I link the output to a dashboard and select a date from drop down

...

---

you should do the filtering in the dashboard site, as the data size is reduced. if not, each line have to duplicate as much as the dropdown options to fulfil the requirement.

@Yuri24 wrote:
...

However, the 15day look back timeframe should be dynamic for any date selected.

...

---

if you refer to dynamic, you can try to use app function.

or the interface.

change the formula to this any date also can. It just easy for change later.

add Date and action tool as follow. (both in interface section)

how to use it:

beside the run button it will have a magic wand. press that to process.

and, here the lessons if you interested.
Interactive Lessons - Alteryx Community

[Yuri24]

Thanks but looks like it wont work well for same dates or duplicate dates

[Yuri24]

Thanks, but the date if I use interaction seeks for manual entry. If I publish this on a server its got to be more dynamic