Thank you for your response.
I began by constructing the basic setup with Today().
So it looked like this:
([Date] > DATEADD('day', -60, Today() )) AND ([Date] <= DATEADD('day', -30, Today())
([(Date]> DATEADD('day', -30, Today())) AND ([Date] <= Today())
This was correct for one value, Today. I want to have a graph over time where the date being compared to is all the dates in the time dimension used.
I also created a separate date table in Excel and used that as a separate data source but because my original data is JSON, I cannot do a cross-database join with Excel and thus cannot join the calendar date table to the JSON data source [Date] field.
I am currently using data blending with the calendar date table with the relationship being [Calendar date] ~ JSON data source [Date] which is leading me to the same problem as before.
Small success, I think.
I turned my Excel Data into JSON data and I am using that as a secondary data source in my data blend. I believe I am heading in the correct direction but I am having trouble setting up the formulas in the correct fashion. From what I understand from https://onlinehelp.tableau.com/current/pro/desktop/en-us/multiple_connections.htm
the data blend should act as a Left Join from the primary data source, which is what I want I think.
I want the calendar date to be an objective data source that is used as the date dimension. Then for each date in the calendar date dimension it checks all of the visits data to see if there is a true statement for:
IF ([Date] > DATEADD('day', -60, CALENDER DATE )) AND ([Date] <= DATEADD('day', -30, CALENDAR DATE)
THEN [Id (Customer)]
IF ([(Date]> DATEADD('day', -30, CALENDER DATE)) AND ([Date] <= CALENDER DATE)
THEN [Id (Customer)]
I have attached the packaged workbook.
I want to find Retained Customer Rate over time.
Customers who had at least one visit between 60 and 30 days previous to the date in question are listed as ACTIVE.
Customers who had at least one visit between 60 and 30 days previous to the date in question and then had at least one visit between 30 days previous and the date in question are listed as RETAINED.
Retained Customer Rates = DistinctCount(RETAINED)/DistinctCount(ACTIVE)